Tuesday, June 30, 2009

Solution for T-SQL challange #10

Hi.
this was the very interesting challange.i enjoy most to solve it.i can't solve it easily and have to get help from friend. This is the solution for T-SQL challange #10.To view challange click here.
on this site jacob post good challnge regarding SQL server problems.you can find more challanges on link provided.
Problem:
(1) perform horizontal sorting on sql table.
(2)Using simple query.
Given Data.

DECLARE @t TABLE
(
c1 CHAR(1),
c2 CHAR(1),
c3 CHAR(1)
)
insert into @t ( c1, c2, c3 )
values ( '2', '1', '3' )
insert into @t ( c1, c2, c3 )
values ( '3', '2', '1' )
insert into @t ( c1, c2, c3 )
values ( 'Z', 'X', 'Y' )
insert into @t ( c1, c2, c3 )
values ( 'B', 'C', 'D' )
insert into @t ( c1, c2, c3 )
values ( 'Y', 'Z', 'X' )
insert into @t ( c1, c2, c3 )
values ( 'B', 'C', 'A' )
SELECT *
FROM @t ;


Solution :


WITH FirstTable
AS ( SELECT *
FROM ( SELECT c1,
c2,
c3,
ROW_NUMBER() OVER ( ORDER BY ( SELECT 0
) ) as 'RN'
FROM @t
) AS t
CROSS JOIN ( SELECT 1 AS part
UNION ALL
SELECT 2
UNION ALL
SELECT 3
) AS n
--Cross join to convert each row value in Diagonal column for further Use
) ,
SecondTable
AS ( SELECT RN,
c1,
c2,
c3,
CASE part
WHEN 1 THEN c1
WHEN 2 THEN c2
WHEN 3 THEN c3
END AS [Values]
FROM FirstTable
--Converts Each Row value as Single Column
) ,
ThirdTable
AS ( SELECT RN AS PK,
c1,
c2,
c3,
[VALUES],
ROW_NUMBER() OVER ( PARTITION BY RN ORDER BY [Values] ) as 'RN'
FROM SecondTable
--perform actual order by operation and set approprite Column number by Row_num function
)
SELECT DISTINCT
[1] AS c1,
[2] AS c2,
[3] AS c3
FROM ThirdTable PIVOT ( MIN([Values]) FOR RN IN ( [1], [2], [3] ) ) AS pv
ORDER BY c1,
c2,
c3 ;


Thanks.