To convert rows to columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

To convert rows to columns

Author

Topic

san2008
Starting Member

4 Posts

Posted - 2008-08-27 : 11:35:58

I have a database which contains tables with dynamic no. of columns.

I had seen a lot of articles on the same but couldn't find any of them graspable or suitable (even the ones where some pivot is used,since I am new to this). I went through the pivot example at SQL Books Online but it was for aggregate functions and i can't understand how do i bring about the foll. output(for RS table data):-

LandArea City Price
650 Miami 30000
250 Kansas 15000

Some guidance on this issue will be greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 13:23:25

without some source data its hard to tell what you're trying to do. Can you provide some sample data from your table please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 13:24:31

May be you want this:-

SELECT LandArea, City, SUM(Price)
FROM YourTable
GROUP BY LandArea, City

san2008
Starting Member

4 Posts

Posted - 2008-08-30 : 11:23:03

quote:
Originally posted by visakh16

without some source data its hard to tell what you're trying to do. Can you provide some sample data from your table please?

Sorry but I myself am confused how all the data i had entered has gone.
Here is the data that i have to use

Table- CM
CM_ID FieldName
10001 LandArea
10002 City
10003 Price

Table- TableMaster
TM_ID Table_ID Column_ID
10001 RES 10001
10002 RNT 10002
10003 RES 10003
10004 RES 10002
10005 RNT 10001
10006 RNT 10003

Table- TableColumnValueMaster
TCVM_ID TM_ID Value
100001 10001 650
100002 10003 30000
100003 10004 Miami
100004 10001 250
100005 10003 15000
100006 10004 Kansas
100007 10005 450
100008 10002 Kansas
100009 10006 20000

The columns CM_ID and Column_ID are PK and FK columns.
And this is the output that is needed when 'RES' is the table name whose data we want.

LandArea City Price
650 Miami 30000
250 Kansas 15000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-30 : 14:07:53

[code]SELECT MAX(CASE WHEN t.FieldName='LandArea' THEN t.Value ELSE NULL END) AS LandArea,
SELECT MAX(CASE WHEN t.FieldName='City' THEN t.Value ELSE NULL END) AS City,
SELECT MAX(CASE WHEN t.FieldName='Price' THEN t.Value ELSE NULL END) AS Price
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY tcvm.TM_ID ORDER BY tcvm.TCVM_ID ) AS RowNo,cm.FieldName,tcvm.Value,tm.Table_ID
FROM TableMaster tm
INNER JOIN CM cm
ON cm.CM_ID=tm.Column_ID
INNER JOIN TableColumnValueMaster tcvm
ON tcvm.TM_ID =tm.TM_ID)t
GROUP BY t.Table_ID,t.RowNo[/code]

san2008
Starting Member

4 Posts

Posted - 2008-08-31 : 13:38:40

quote:

Originally posted by visakh16

SELECT MAX(CASE WHEN t.FieldName='LandArea' THEN t.Value ELSE NULL END) AS LandArea,
SELECT MAX(CASE WHEN t.FieldName='City' THEN t.Value ELSE NULL END) AS City,
SELECT MAX(CASE WHEN t.FieldName='Price' THEN t.Value ELSE NULL END) AS Price
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY tcvm.TM_ID ORDER BY tcvm.TCVM_ID ) AS RowNo,cm.FieldName,tcvm.Value,tm.Table_ID
FROM TableMaster tm
INNER JOIN CM cm
ON cm.CM_ID=tm.Column_ID
INNER JOIN TableColumnValueMaster tcvm
ON tcvm.TM_ID =tm.TM_ID)t
GROUP BY t.Table_ID,t.RowNo

Hi,
I ran the queries that you had provided but there were some errors and it did not execute.
Here is the exact error that SQL Server gave:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'SELECT'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-31 : 23:57:26

aha...copy paste problem

SELECT MAX(CASE WHEN t.FieldName='LandArea' THEN t.Value ELSE NULL END) AS LandArea,
 MAX(CASE WHEN t.FieldName='City' THEN t.Value ELSE NULL END) AS City,
 MAX(CASE WHEN t.FieldName='Price' THEN t.Value ELSE NULL END) AS Price
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY tcvm.TM_ID ORDER BY tcvm.TCVM_ID ) AS RowNo,cm.FieldName,tcvm.Value,tm.Table_ID
FROM TableMaster tm
INNER JOIN CM cm
ON cm.CM_ID=tm.Column_ID
INNER JOIN TableColumnValueMaster tcvm
ON tcvm.TM_ID =tm.TM_ID)t
GROUP BY t.Table_ID,t.RowNo

Piyush
Starting Member

8 Posts

Posted - 2011-07-25 : 01:54:27

Its a very easy way to transform the rows to column attributes, just go through the link:
unspammed

Piyush Bajaj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources