Help with PIVOT query
This is a bit messy to get the final result because you have multiple SCHEMA_VER for each date. Before I demonstrate how to do this with dynamic SQL, I'll first show how to do it with static code to get the logic correct. In order to get the final result you can utilize both pivot and unpivot.
But first, I'd change your original query to use the following:
select
row_number() over (
partition by CONVERT(varchar(10), SCHEMA_VER_DATE, 110) order by SCHEMA_VER_MAJOR, SCHEMA_VER_MINOR, SCHEMA_VER_SUB
) as minrownum
, row_number() over (
partition by CONVERT(varchar(10), SCHEMA_VER_DATE, 110) order by SCHEMA_VER_MAJOR desc, SCHEMA_VER_MINOR desc, SCHEMA_VER_SUB desc
) as maxrownum
,CONVERT(varchar(10), SCHEMA_VER_DATE, 110) as UPG_DATE
,CONVERT(varchar(1), SCHEMA_VER_MAJOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_MINOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_SUB) as SCHEMA_VER
from audit_schema_version
where SCHEMA_VER_REMARK like 'Stored procedure build';
See SQL Fiddle with Demo. I used row_number() to get the first and the last SCHEMA_VER for each date. This is needed so you can concatenate only those values together for the comment.
Then I would use a temp table to store the rows that have a minrownum and maxrownum of 1. The temp table would contain the upg_date and the comment. This comment column contains a concatenated string of the pair of the SCHEMA_VER for each date.
create table #srcData
(
upg_date varchar(10),
comment varchar(500)
);
The code to populate the temp table would then be:
;with cte as
(
select
row_number() over (
partition by CONVERT(varchar(10), SCHEMA_VER_DATE, 110) order by SCHEMA_VER_MAJOR, SCHEMA_VER_MINOR, SCHEMA_VER_SUB
) as minrownum
, row_number() over (
partition by CONVERT(varchar(10), SCHEMA_VER_DATE, 110) order by SCHEMA_VER_MAJOR desc, SCHEMA_VER_MINOR desc, SCHEMA_VER_SUB desc
) as maxrownum
,CONVERT(varchar(10), SCHEMA_VER_DATE, 110) as UPG_DATE
,CONVERT(varchar(1), SCHEMA_VER_MAJOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_MINOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_SUB) as SCHEMA_VER
from audit_schema_version
where SCHEMA_VER_REMARK like 'Stored procedure build'
)
insert into #srcData
select distinct
c1.UPG_DATE,
comment
= STUFF((
SELECT ' - ' + c2.SCHEMA_VER
FROM cte c2
WHERE (c2.minrownum = 1 or c2.maxrownum = 1)
and c1.upg_date = c2.upg_date
order by c2.minrownum
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
from cte c1
where c1.minrownum = 1 or c1.maxrownum = 1;
This first pass through your data gets you:
| upg_date | comment |
|------------|-------------------|
| 03-23-2015 | 2.13.0 |
| 04-05-2013 | 1.6.13 - 1.16.13 |
| 04-17-2014 | 1.16.13 - 2.7.0 |
| 12-09-2014 | 2.8.0 - 2.12.0 |
Now you still need to get a count of each date for the year and the full concatenated comment. This would be where unpivot comes into play. You could use the following code to create the full comment for each year and to get the count.
select distinct
Yr = right(s1.upg_date, 4),
cnt = count(*) over(partition by right(s1.upg_date, 4)),
fullcomment
= STUFF((
SELECT '; ' + s2.comment
FROM #srcData s2
WHERE right(s1.upg_date, 4) = right(s2.upg_date, 4)
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
from #srcData s1;
See SQL Fiddle with Demo. The data now looks like:
| Yr | cnt | fullcomment |
|------|-----|-----------------------------------|
| 2013 | 1 | 1.6.13 - 1.16.13 |
| 2014 | 2 | 1.16.13 - 2.7.0; 2.8.0 - 2.12.0 |
| 2015 | 1 | 2.13.0 |
As you can see you've got multiple columns that need to be pivoted, so you can unpivot both the fullcomment and cnt column into multiple rows. This can be done using the UNPIVOT function or CROSS APPLY. I'd prefer cross apply here because you'll want to concatenate values together to create the new column names:
;with cte as
(
select distinct
Yr = right(s1.upg_date, 4),
cnt = count(*) over(partition by right(s1.upg_date, 4)),
fullcomment
= STUFF((
SELECT '; ' + s2.comment
FROM #srcData s2
WHERE right(s1.upg_date, 4) = right(s2.upg_date, 4)
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
from #srcData s1
)
select [2015], [2015_comment], [2014], [2014_comment], [2013], [2013_comment]
from
(
select c.col, val
from cte d
cross apply
(
values
(Yr, cast(cnt as nvarchar(50))),
(Yr+'_comment', fullcomment)
) c (col, val)
) d
pivot
(
max(val)
for col in ([2015], [2015_comment], [2014], [2014_comment], [2013], [2013_comment])
) piv;
See SQL Fiddle with Demo.
Once you've got the logic, you can easily convert this to dynamic SQL.
-- get list of the columns
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(col)
from #srcData
cross apply
(
select right(upg_date, 4), right(upg_date, 4), 2 union all
select right(upg_date, 4), right(upg_date, 4)+'_comment', 1
) c (yr, col, so)
group by yr, col, so
order by yr desc, so desc
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'SELECT ' + @cols + '
from
(
select c.col, val
from
(
select distinct
Yr = right(s1.upg_date, 4),
cnt = count(*) over(partition by right(s1.upg_date, 4)),
fullcomment
= STUFF((
SELECT ''; '' + s2.comment
FROM #srcData s2
WHERE right(s1.upg_date, 4) = right(s2.upg_date, 4)
FOR XML PATH(''''), TYPE).value(''.[1]'', ''nvarchar(max)''), 1, 2, '''')
from #srcData s1
) d
cross apply
(
values
(Yr, cast(cnt as nvarchar(50))),
(Yr+''_comment'', fullcomment)
) c (col, val)
) x
pivot
(
max(val)
for col in (' + @cols + ')
) p '
exec sp_executesql @query;
See SQL Fiddle with Demo. Both versions will give you the result:
| 2015 | 2015_comment | 2014 | 2014_comment | 2013 | 2013_comment |
|------|--------------|------|-----------------------------------|------|-------------------|
| 1 | 2.13.0 | 2 | 1.16.13 - 2.7.0; 2.8.0 - 2.12.0 | 1 | 1.6.13 - 1.16.13 |
Adding explanation and a fiddle: http://sqlfiddle.com/#!6/c92b2/5.
The query bellow:
1. uses a subquery to select the min and max versions by date (min and max are applied to integers to guarantee that for instance 6 < 16)
2. Then Selects the year (to group later), Date (to order) and the min - max versions
SELECT LEFT(UPG_DATE, 4) AS Year
, UPG_DATE
, CONVERT(varchar(1), MIN_VER/1000000) + '.' + CONVERT(varchar(2), (MIN_VER/1000 - (MIN_VER/1000000)*1000)) + '.' + CONVERT(varchar(2), MIN_VER%1000)
+ ' - ' + CONVERT(varchar(1), MAX_VER/1000000) + '.' + CONVERT(varchar(2), (MAX_VER/1000 - (MAX_VER/1000000)*1000)) + '.' + CONVERT(varchar(2), MAX_VER%1000) AS Versions
INTO #Versions
FROM (
SELECT CONVERT(varchar(10), SCHEMA_VER_DATE, 112) as UPG_DATE
, MIN(SCHEMA_VER_MAJOR*1000000 + SCHEMA_VER_MINOR*1000 + SCHEMA_VER_SUB) AS MIN_VER
, MAX(SCHEMA_VER_MAJOR*1000000 + SCHEMA_VER_MINOR*1000 + SCHEMA_VER_SUB) AS MAX_VER
FROM audit_schema_version
WHERE SCHEMA_VER_REMARK like 'Stored procedure build'
GROUP BY CONVERT(varchar(10), SCHEMA_VER_DATE, 112)
) Versions;
Next, as each column will be repeated (year and year_COMMENT), two columns are selected to identify the data. The number of dates are counted to know the number of upgrades and Versions are grouped by year, stuffing so it gets all into one line. This gives us the final table that will be used to pivot.
SELECT Year, Year + '_COMMENT' as Year_COMMENT
, COUNT(Year) AS Upgrades
, STUFF((SELECT ' ; ' + SUB.Versions
FROM #Versions SUB
WHERE SUB.Year = V.Year
ORDER BY UPG_DATE ASC
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(2000)')
,1,3,'') Versions
INTO #GroupedResults
FROM #Versions V
GROUP BY Year
SELECT * FROM #GroupedResults
Here are the results:
| Year | Year_COMMENT | Upgrades | Versions |
|------|--------------|----------|----------------------------------|
| 2013 | 2013_COMMENT | 1 | 1.6.13 - 1.16.13 |
| 2014 | 2014_COMMENT | 2 | 1.16.13 - 2.7.0 ; 2.8.0 - 2.12.0 |
| 2015 | 2015_COMMENT | 1 | 2.13.0 - 2.13.0 |
Next, a variable is filled with the columns, ordered as we want to display them:
DECLARE @cols VARCHAR(1000),
@finalQuery VARCHAR(2000)
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(YEAR) + ',' + QUOTENAME(YEAR + '_COMMENT')
FROM #GroupedResults
GROUP BY YEAR
ORDER BY YEAR DESC
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(2000)')
,1,1,'')
Finally, the bellow query uses cross apply so we get:
1. The col column filled with the Year and Year_COMMENT values
2. The value column filled with the number of upgrades, in the lines corresponding to the years, and versions values, in the lines corresponding to the Year_COMMENTs
A pivot is used over the two resulting columns giving us the values (number of upgrades alternating with versions) over the col (Years alternanting with Year_COMMENTs)
set @finalQuery = N'SELECT ' + @cols + N' from
(
select col, value
from #GroupedResults
cross apply
(
SELECT CAST(Upgrades AS VARCHAR(200)), Year
UNION ALL
SELECT CAST(Versions AS VARCHAR(200)), Year_COMMENT
) c (value, col)
) x
pivot
(
Min(value)
for col in (' + @cols + N')
) p1
; '
EXEC (@finalQuery);
DROP TABLE #Versions;
DROP TABLE #GroupedResults;
This returns the following results:
| 2015 | 2015_COMMENT | 2014 | 2014_COMMENT | 2013 | 2013_COMMENT |
|------|-----------------|------|----------------------------------|------|------------------|
| 1 | 2.13.0 - 2.13.0 | 2 | 1.16.13 - 2.7.0 ; 2.8.0 - 2.12.0 | 1 | 1.6.13 - 1.16.13 |