0

This question already has an answer here:

I have DDL like the below, which returns data like such:

GP17    GP16
0       103
90      0
375     0

I am wanting to pivot the data somehow so that it returns only one row, like such:

Jan 17 ---- Jan 16 ---- Feb 17 ---- Feb 16 ---- Mar 17 ---- Mar 16
0            103         90          0           375         0   

Now this is relevant to the date range being passed in below, for example, if the date range being passed in is 20170401 20170630 then I would want the dates to read Apr 17, Apr 16, May 17, May 16, Jun 17, Jun 16

How can this be achieved with SQL Server 2008 R2 and the DDL below?

Declare @Test Table
(
    dname varchar(100)
    ,tr float
    ,dc float
    ,sdate date
)


Insert Into @Test (dname, tr, dc, sdate) Values
('Green', 100.00, 10.00, '20170101'), ('Green', 200.00, 15.00, '20170201'), ('Green', 200.00, 10.00, '20170215')
,('Green', 10.00, 0.00, '20160101'), ('Green', 15.00, 10.00, '20160130'), ('Green', 110.00, 22.00, '20160101')

Declare @startdate date = '20170101', @enddate date = '20170331'

SELECT 
ROUND(SUM(ISNULL(tr17,0))-SUM(ISNULL(DC17,0)),0) As GP17,
ROUND(SUM(ISNULL(tr16,0))-SUM(ISNULL(DC16,0)),0) As GP16
FROM
(
SELECT
ZT1.dname,
SUM(ISNULL(ZT1.tr,0)) AS tr17,
0 As DC17,
0 As DC16,
0 AS tr16,
sdate As activityDate
FROM @Test AS ZT1
WHERE ZT1.sdate >= @startdate
AND ZT1.sdate <= @enddate
GROUP BY ZT1.dname, ZT1.sdate
UNION ALL
Select
t1.dname,
0 As tr17,
SUM(ISNULL(t1.dc,0)) As DC17,
0 As DC16,
0 AS tr16,
sdate as activityDate
FROM @Test t1
WHERE t1.sdate >= @startdate
AND t1.sdate <= @enddate
Group By t1.dname, t1.sdate
UNION ALL
SELECT
ZT1.dname,
0 As tr17,
0 As DC17,
0 As DC16,
SUM(ISNULL(ZT1.tr,0)) AS tr16,
sdate As activityDate
FROM @Test AS ZT1
WHERE ZT1.sdate >= DateAdd(yy, -1, @startdate)
AND ZT1.sdate <= DateAdd(yy, -1, @enddate)
GROUP BY ZT1.dname, ZT1.sdate
UNION ALL
Select
t1.dname,
0 As tr17,
0 As DC17,
SUM(ISNULL(t1.dc,0)) As DC16,
0 AS tr16,
sdate as activityDate
FROM @Test t1
WHERE t1.sdate >= DateAdd(yy, -1, @startdate)
AND t1.sdate <= DateAdd(yy, -1, @enddate)
Group By t1.dname, t1.sdate
) AS A
GROUP BY YEAR(activityDate), MONTH(activityDate)

marked as duplicate by Paul White sql-server Oct 13 '17 at 8:24

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1

A PIVOT solution requires a well-know number of columns.

If you're looking for an answer that covers no matter which dates, I'd suggest you to search for 'Dynamic pivot'

;WITH data AS
(
    SELECT  dname, 
            tr - dc as result,
            LEFT(DATENAME(MONTH, sdate), 3) + '-' + RIGHT(CAST(YEAR(sdate) AS varchar(10)), 2) as DateName
    FROM    @Test
)
SELECT dname, [Jan-16],[Jan-17],[Feb-16],[Feb-17]
FROM   (SELECT * FROM data) src
PIVOT  (SUM(result) FOR DateName IN([Jan-16],[Jan-17],[Feb-16],[Feb-17])) pvt;

GO
dname | Jan-16 | Jan-17 | Feb-16 | Feb-17
:---- | -----: | -----: | -----: | -----:
Green |    103 |     90 |   null |    375

dbfiddle here

Not the answer you're looking for? Browse other questions tagged or ask your own question.