[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

How to query data vertically and show horizontal data

Hi all!

My_Table
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.

Thx
Chad
0
Chadak
Asked:
Chadak
2 Solutions
 
stevetheskiCommented:
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++)
        {
            table.Columns.Add(Convert.ToString(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];
        }
        table.Rows.Add(r);
    }
    ds.Tables.Add(table);
}
0
 
PSSUserCommented:
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.
0
 
stevetheskiCommented:
ok you had me wondering again and i found this article

http://sqljunkies.com/WebLog/mattnunn/archive/2003/08/07/173.aspx

maybe that will work for ya
0
 
BillAn1Commented:
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)
0
 
ChadakAuthor Commented:
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 :)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now