Friday 16 March 2018

Multiple column pivot in sql server using sql query

/////////////// Function using CROSS APPLY


DECLARE @Temp_Table TABLE (Branch varchar(9), Category varchar(9), Sales INT,Stock INT,Target INT)

INSERT INTO @Temp_Table (Branch, Category, Sales, Stock,Target)
VALUES
    ( 'Gurgaon', 'Spanel', 10,4,15),
    ( 'Gurgaon', 'AC', 11,7,14),
    ( 'Gurgaon', 'Ref', 7,2,10),
    ( 'Delhi', 'Spanel',20,4,17),
    ( 'Delhi', 'AC', 5,2,12),
    ( 'Delhi', 'Ref', 10,12,22)

---------- Check Table Value before pivot
select * from @Temp_Table

---------- Check Table Value After pivot

 Select BRANCH,COL,[Spanel],[AC],[Ref] from (
    select Branch,Category,COL,VAL from @Temp_Table  CROSS APPLY (VALUES ('Sales',Sales),  ('Stock',Stock), ('Target',Target))CS (COL,VAL))T
    PIVOT (MAX(VAL) FOR Category IN ([Spanel],[AC],[Ref]))PVT



No comments:

Post a Comment

Excel Sort values in ascending order using function TEXTJOIN

 Excel ::  Text ::  1,3,5,2,9,5,11 Result :: 1,2,3,5,5,9,11 Formula ::     TEXTJOIN ( ",",1,SORT(MID(SUBSTITUTE( A1 ,","...