put row data in 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

General SQL Server Forums

New to SQL Server Programming

put row data in columns

Author

Topic

ng
Starting Member

1 Post

Posted - 2008-10-22 : 11:02:08

Hello everybody,

I am new to sql serever and need help with a query that will give output like this -

Input data from Table = 'MASTER"

------------------------------------------------------

Key Col1 Col2

1 A 01-01-2001

1 B 01-05-2002

Expected Output Data

--------------------------------------

Key Col1.01 Col2.01 Col1.02 Col2.02
1 A 01-01-2001 B 01-05-2002

I have tried using the following sql -

select A.Key, A.Col1, A.Col2, B.Col1, B.Col2

from MASTER A, MASTER B

where A.key = B.key

This query gives me the reocrd i want but also gives 3 more records due to the full join.

Please advice as to how I can get only 1 record with the expected result.

Thanks and Regards

- NG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 11:05:47

[code]SELECT Key,
MAX(CASE WHEN Col1='A' THEN Col1 ELSE NULL END) AS [Col1.01],
MAX(CASE WHEN Col1='A' THEN Col2 ELSE NULL END) AS [Col2.01],
MAX(CASE WHEN Col1='B' THEN Col1 ELSE NULL END) AS [Col1.02],
MAX(CASE WHEN Col1='B' THEN Col2 ELSE NULL END) AS [Col2.02]
FROM MASTER
GROUP BY Key[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources