Convert column to rows - 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

Transact-SQL (2005)

Convert column to rows

Author

Topic

sql1983
Starting Member

3 Posts

Posted - 2007-10-31 : 13:03:26

Hi All,
i googled for this n its making more confusing so i'm posting it here,Thanks for ur patience in advance.
I have data like below
abbc 1 a
abbc 1 b
abbc 1 c
xyz 2 a
xyz 2 e
xyz 2 d
xyz 2 f
ijk 1 x
ijk 1 y
ijk 1 z
ijk 1 a
ijk 1 s

The thing is first 2 columns are primary and third column can be anything
My output should look like
abbc 1 a b c 0 0
xyz 2 a e d f 0
ijk 1 x y z a s
Any idea is appreciated.
Thanks in Advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-31 : 21:47:13

[code]DECLARE @sample TABLE
(
col1 varchar(5),
col2 int,
col3 varchar(5)
)

INSERT INTO @sample
SELECT 'abbc', 1, 'a' UNION ALL
SELECT 'abbc', 1, 'b' UNION ALL
SELECT 'abbc', 1, 'c' UNION ALL
SELECT 'xyz', 2, 'a' UNION ALL
SELECT 'xyz', 2, 'e' UNION ALL
SELECT 'xyz', 2, 'd' UNION ALL
SELECT 'xyz', 2, 'f' UNION ALL
SELECT 'ijk', 1, 'x' UNION ALL
SELECT 'ijk', 1, 'y' UNION ALL
SELECT 'ijk', 1, 'z' UNION ALL
SELECT 'ijk', 1, 'a' UNION ALL
SELECT 'ijk', 1, 's'

SELECT col1, col2,
[1] = ISNULL([1], '0'),
[2] = ISNULL([2], '0'),
[3] = ISNULL([3], '0'),
[4] = ISNULL([4], '0'),
[5] = ISNULL([5], '0')
FROM
(
SELECT col1, col2, col3, row = row_number() OVER (PARTITION BY col1, col2 ORDER BY col3)
FROM @sample
) a
pivot
(
MAX(col3)
FOR row IN ([1], [2], [3], [4], [5])
) p

/*
col1 col2 1 2 3 4 5
----- ----------- ----- ----- ----- ----- -----
abbc 1 a b c 0 0
ijk 1 a s x y z
xyz 2 a d e f 0

(3 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources