My data looks like this:
| ACCNT | AMOUNT | TYPE | YEAR |
|-------|--------|------|------|
| 458 | 168.80 | A | 2014 |
| 458 | 282.77 | A | 2015 |
| 458 | 64.70 | B | 2015 |
| 458 | 91.25 | A | 2016 |
| 458 | 398.00 | B | 2016 |
| 458 | 104.45 | C | 2016 |
| 927 | 445.00 | B | 2014 |
| 927 | 25.00 | A | 2015 |
| 927 | 10.00 | NULL | 2015 |
| 927 | 132.00 | A | 2016 |
| 927 | 381.40 | B | 2016 |
| 927 | 210.00 | C | 2016 |
...
An Accnt might not have a value for each Year, and the Type will occasionally be NULL. I want to have just one row per Accnt, and combine the Amount & Type columns under a Year heading, i.e. to get it looking like this:
| ACCNT | 2014 | 2015 | 2016 |
|-------|-----------|--------------------|----------------------------|
| 458 | 168.80,A | 282.77,A;64.70,B | 91.25,A;398.00,B;104.45,C |
| 927 | 445.00,B | 25.00,A;10.00,NULL | 132.00,A;381.40,B;210.00,C |
...
I can pivot on the [Year]...
SELECT * FROM
(SELECT [Accnt], [Amount], [Type], [Year] FROM data_table)
AS Source
PIVOT
(
MAX([Amount]) FOR [Year] IN ([2014],[2015],[2016])
) AS PVT
...but still end up with multiple rows per Accnt.
The following gets closer to the format I'm looking for...
SELECT Accnt, [1] AS '2014', [2] AS '2015', [3] AS '2016'
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Accnt ORDER BY [Year] Asc) AS AccountID,
Accnt, CONVERT(nvarchar,Amount) + ',' + [Type] AS Amount_And_Type
FROM data_table) PIVOT (MAX(Amount_And_Type)
FOR AccountID IN ([1], [2], [3])) AS data_pvt
...but it's just giving me the first three rows for each Accnt:
| ACCNT | 2014 | 2015 | 2016 |
|-------|-----------|----------|---------|
| 458 | 168.80,A | 282.77,A | 67.40,B |
| 927 | 445.00,B | 25.00,A | NULL |
Is what I'm trying to do even possible with a Pivot?
You can query as below:
;with cte as (
select Accnt, iif([2014] is null,null, concat([2014],',',[Type])) as [2014a]
,iif([2015] is null,null, concat([2015],',',[Type])) as [2015a]
,iif([2016] is null,null, concat([2016],',',[Type])) as [2016a]
from youracct
pivot(max(amount) for [year] in ([2014],[2015],[2016])) p
)
select c.Accnt, [2014]= stuff(( Select ';'+[2014a] from cte where Accnt = c.Accnt for xml path('')),1,1,'')
,[2015]=stuff(( Select ';'+[2015a] from cte where Accnt = c.Accnt for xml path('')),1,1,'')
,[2016]=stuff(( Select ';'+[2016a] from cte where Accnt = c.Accnt for xml path('')),1,1,'')
from cte c
group by c.Accnt
But dynamic sql for this approach is bit complex but we can do..
Output as below:
+-------+---------+-----------------+------------------------+
| Accnt | 2014 | 2015 | 2016 |
+-------+---------+-----------------+------------------------+
| 458 | 168.8,A | 282.77,A;64.7,B | 91.25,A;398,B;104.45,C |
| 927 | 445,B | 10,;25,A | 132,A;381.4,B;210,C |
+-------+---------+-----------------+------------------------+