January 27, 2010 at 3:21 am
Hello Friends,
This is my first post to this forum. Please help
I have table called [testregister] following are the contents of it
Column1 Column2
AI1 40001
AI1 40002
AI1 40003
AI1 40004
AI1 40005
AI1 40006
AI2 41001
AI2 41002
AI2 41003
AI2 41004
AI2 41005
AI2 41006
AI3 42001
AI3 42002
AI3 42003
AI3 42004
AI3 42005
AI3 42006
I want display this result as following
AI1 AI2 AI3
40001 41001 42001
40002 41002 42002
40003 41003 42003
40004 41004 42004
40005 41005 42005
40006 41006 42006
I did try using Pivot but i could not find desired output.
Please let me know if any one have answer.
January 27, 2010 at 4:11 am
DECLARE @table table
(
IDVARCHAR(100),
ZIPINT
)
INSERT INTO @table
SELECT
'AI1', 40001
UNION ALL
SELECT
'AI1', 40002
UNION ALL
SELECT
'AI1' ,40003
UNION ALL
SELECT
'AI1' ,40004
UNION ALL
SELECT
'AI1', 40005
UNION ALL
SELECT
'AI1', 40006
UNION ALL
SELECT
'AI2', 41001
UNION ALL
SELECT
'AI2', 41002
UNION ALL
SELECT
'AI2', 41003
UNION ALL
SELECT
'AI2', 41004
UNION ALL
SELECT
'AI2', 41005
UNION ALL
SELECT
'AI2', 41006
UNION ALL
SELECT
'AI3', 42001
UNION ALL
SELECT
'AI3', 42002
UNION ALL
SELECT
'AI3', 42003
UNION ALL
SELECT
'AI3', 42004
UNION ALL
SELECT
'AI3',42005
UNION ALL
SELECT
'AI3', 42006
IF OBJECT_ID('tempdb..#temp')IS NOT NULL
DROP TABLE #temp;
SELECT AI1,AI2, AI3,ROW_NUMBER() OVER(ORDER BY CASE WHEN [AI1] IS NULL THEN 100000 ELSE [AI1] END ) AS A1,
ROW_NUMBER() OVER(ORDER BY CASE WHEN [AI2] IS NULL THEN 100000 ELSE [AI2]END) AS A2,
ROW_NUMBER() OVER(ORDER BY CASE WHEN [AI3] IS NULL THEN 100000 ELSE [AI3]END) AS A3
INTO #temp
FROM
(
SELECT ID,ZIP,ZIP AS A FROM @table
)A
PIVOT
(
MAX(ZIP) FOR ID IN([AI1],[AI2],[AI3])
)PIVOTS
UPDATE
T1
SET
AI2=T2.AI2,
AI3=T3.AI3
FROM
#tempT1
INNER JOIN
#tempT2
ON
T1.A1=T2.A2
INNER JOIN
#tempT3
ON
T1.A1=T3.A3
SELECT AI1,AI2,AI3 FROM #temp WHERE AI1 IS NOT NULL
Regards,
Mitesh OSwal
+918698619998
January 27, 2010 at 4:21 am
If you just want to transform rows into column to show in Cross-Tab report, there is an alternate way.
Use Cross-Tab report to achieve this.
January 27, 2010 at 8:23 am
Thanks Mitesh for quick reply.
is there any simple way of achiving it. actully what ever table i shown is just an example. the real table is bigger than it. concider real table having 32 IDs and 10 Zip for each ID(I hope this is clear for you).
So it is becoming more complicated. So i will appreciate a simple and dynamic way of doing it.
Thanks,
Chaitanya
January 27, 2010 at 8:33 am
If you look in my signature block below, you will see several references to articles. Look at the fifth line, talks about cross tabs and pivots, there are two articles referenced. Take the time to read them and see if they help you with your current problem.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply