January 12, 2010 at 11:20 pm
Hi,
I have one temp table with two columns, contain the values like..
Name Post
---------------------
A 1
A 2
A 3
B 1
B 11
C 21
C 32
C 14
Now I want to show the results as...
Name post1 post2 post3
------------------------------------
A 1 2 3
B 1 11 null
C 21 32 14
Can anyone give me the solution for this one.
Regards,
Ram
January 12, 2010 at 11:44 pm
DECLARE @data
TABLE
(
col1 CHAR NULL,
col2 INT NULL
);
INSERT @data (col1, col2)
VALUES ('A', 1),
('A', 2),
('A', 3),
('B', 1),
('B', 11),
('C', 21),
('C', 32),
('C', 14);
--A 1 2 3
--B 1 11 null
--C 21 32 14
WITH Sequence
AS (
SELECT col1,
col2,
rn = ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2 ASC)
FROM @data
)
SELECT P.col1,
P.[1],
P.[2],
P.[3]
FROM Sequence
PIVOT (
MAX(col2)
FOR rn IN ([1],[2],[3])
) P;
January 17, 2010 at 11:17 pm
Hi,
Thanks for your help. Its working fine.
Regards,
Ram
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply