Column to row

Question

Column to row

ash-222070

Valued Member

Points: 68
More actions
April 13, 2005 at 2:49 pm

#76279

Is there a simple way to show columns as rows in a table.
I got a table looks like this
pk(Auto)    ID      Dataname     Data
1           111     Name         abc
2           111     Address      123, street
3           111     City         Atlanta
4           222     Name         efg
5           222     Address      456, somewhere
6           333     Name         xyz
I want to show end user a report like below
ID    Name      Address           City
111   abc       123, street       Atlanta
222   efg       456, somewhere
333   xyz
Can someone show me how to show this.
Thanks
ash

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 1

Depending on your reporting software, this may be easier to re-organize with it. What are you using to generate reports?

K. Brian Kelley
@kbriankelley

ash-222070 Valued Member Points: 68 More actions · Answer 2

Iam using SQL reporting services, but I need SQL query to create this as view.

Thank you

ash

Sergiy SSC Guru Points: 110192 More actions · Answer 3

You can join table to itself

SELECT MT1.ID, MT1.Data as Name, MT2.Data as Address, MT3.Data as City

FROM MyTable MT1

inner join MyTable MT2 on MT1.ID = MT2.ID

inner join MyTable MT3 on MT2.ID = MT3.ID

where MT1.Dataname = 'Name'

and MT2.Dataname = 'Address'

and MT3.Dataname = 'City'

_____________
Code for TallyGenerator

ash-222070 Valued Member Points: 68 More actions · Answer 4

Thanks Sergiy,

Your solution is great, but each id can have upto 20 to 30 records means that many different fields like name and address.

I'm thinking about something dynamic to get all unique datanames for each id and show the data for each record.

Thanks

ash

ash-222070 Valued Member Points: 68 More actions · Answer 5

One more thing I forgot to mention, all Id's dont have constant number of records or fields.

One id can have 5 records another can have 20 records, so I need to show remaining 15 fields as null fields for id with 5 records.

Thanks

ash

Roy Ashbrook SSChasing Mays Points: 632 More actions · Answer 6

assuming you have to analyze the query results given, dynamic sql is your friend. there may be a better way to get the numbers you want if you posted the query that gives you the numbers above.