Thursday, 13 November 2014

Get Data Vertical to Horizontal form in SqlServer

Here I am explain how to get data  vertical into horizontal Form with a T-SQL Query (max. 3 Columns).
Table
Old View:
StoreNo.
PhoneNo.
1111
0120-2411000
1111
0120-2411001
1111
0120-2411002


New View:
StoreNo
Phone1
Phone2
Phone3
1111
0120-2411000
0120-2411001
0120-2411002

IF OBJECT_ID('Gangwar..#Temp') IS NOT NULL
DROP TABLE  #Temp;
GO
;

CREATE TABLE #Temp
(
    StoreNo  INT
   ,PhoneN0 VARCHAR(20)

)
;

INSERT INTO #Temp (StoreNo,PhoneN0)
           SELECT 1111 , '0120-2411000'
UNION ALL  SELECT 1111 , '0120-2411001'
UNION ALL  SELECT 1111 , '0120-2411002'
;

; WITH CTE AS
( 
      SELECT T.StoreNo , T.PhoneN0
            , RN = ROW_NUMBER() OVER(PARTITION BY T.StoreNo ORDER BY T.PhoneNo)
      FROM  #Temp T
)
SELECT Store
       , [1] AS Phone1
         , [2] AS Phone2
         , [3] AS Phone3
FROM CTE

PIVOT ( MAX(PhoneNr) FOR RN IN ([1],[2],[3])) Pivot_Handle

OutPut :

1 comment:

  1. string g = "select channel from vTable where uid=" + Session["userid"];
    how to use order by command in above statement ?

    ReplyDelete