How to query data vertically and show horizontal data

Posted on 2005-05-10
Last Modified: 2008-02-26
Hi all!

ID             Value     ColumnName   customer
1             'value1'    'name1'           1
2             'value2'    'name2'           1
3             'value3'    'name3'           1
4             'value4'    'name4'           1
5             'value5'    'name5'           1
5             'value1'    'name1'           2

I need to make a query that return this!

Name1   name2  name3   name4  name5
value1   value2    value3   value4    value5

Can't figure how to return this one, also it would be great if this query would also work in access.

Question by:Chadak
    LVL 4

    Assisted Solution

    good luck htis is how i did it in c#

    public DataSet FlipDataSet(DataSet my_DataSet)
        DataSet ds = new DataSet();
        foreach(DataTable dt in my_DataSet.Tables)
            DataTable table = new DataTable();
            for(int i=0; i<=dt.Rows.Count; i++)
            DataRow r;
            for(int k=0; k<dt.Columns.Count; k++)
                r = table.NewRow();
                r[0] = dt.Columns[k].ToString();
                for(int j=1; j<=dt.Rows.Count; j++)
                    r[j] = dt.Rows[j-1][k];
    LVL 10

    Expert Comment

    Actually, the access query is fairly straight forward. It's the SQL Server one that's the problem.

    In MS Access you will need to use a Cross Tab query. I haven't got Access on this PC, so I can't give you an example, but if you go into the deisgn view and change the query type to cross tab it should be fairly straight forward. Let me know if you need any further info for the Access query.
    LVL 4

    Expert Comment

    ok you had me wondering again and i found this article

    maybe that will work for ya
    LVL 17

    Accepted Solution

    a more suscint solution for your particular case is the following. It will return a record set llike

    customer    value1       value2         value3         value4          value5                          
    ----------- ------------- ------------- -------------- ------------- ---------------
    1              name1       name2        name3        name4       name5
    2              name1       NULL           NULL           NULL          NULL

    declare @MySQL varchar(1000)
    set @MySQL = 'select customer'
    select @MySQL = @MySQL + ' ,max(case when Value = ''' + Value + ''' then ColumnName end) as [' +  Value + ']'
    from (select distinct value from My_Table ) sub order by value
    set @MySQL = @MySQL + 'from My_Table group by customer'
    print @MySQL
    exec (@MySQL)
    LVL 1

    Author Comment

    I don't know what to do here as an answer, no answer solve my problem, but there is some solution provided that answered the question

    So still i'll close this question
    Thanks guys for the help :)

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now