We can create pivoting on multiple columns as below.
Query:
Query:
IF OBJECT_ID('tempdb..#tbl') IS NOT NULL BEGIN
DROP TABLE #tbl
END
CREATE TABLE #tbl(
Dt DATE,
Acnt INT,
BCnt INT,
CCnt INT
)
DECLARE
@coldata VARCHAR(500),
@sql varchar(max)
INSERT INTO #tbl
VALUES('2016-01-01',10,20,30),
('2016-01-02',40,50,60)
select * from #tbl
SELECT @coldata = COALESCE(@coldata + '], [', '') + FORMAT(Dt, 'yyyy-MM-dd') FROM #tbl
ORDER BY dt desc
SELECT @coldata = '[' + @coldata +']'
SET @sql = 'SELECT Dt=col, ' + @coldata + '
FROM
(
SELECT FORMAT(Dt, ''yyyy-MM-dd'') AS Dt, col, value FROM #tbl
CROSS APPLY
(
SELECT
''Acnt'',cast(Acnt as varchar(10)) UNION ALL
SELECT
''BCnt'', cast(BCnt as varchar(10)) UNION ALL
SELECT
''CCnt'', cast(CCnt as varchar(10))
) c (col,value)
)
d
PIVOT
(
MAX(value)
FOR Dt IN (' + @coldata + ')
)
AS piv'
EXECUTE (@SQL);
IF OBJECT_ID('tempdb..#tbl') IS NOT NULL BEGIN
DROP TABLE #tbl
END
Result:
Actual result
With Pivot