Help with PIVOT query

I have a table with below structure :

CREATE TABLE [dbo].[AUDIT_SCHEMA_VERSION](
    [SCHEMA_VER_MAJOR] [int] NOT NULL,
    [SCHEMA_VER_MINOR] [int] NOT NULL,
    [SCHEMA_VER_SUB] [int] NOT NULL,
    [SCHEMA_VER_DATE] [datetime] NOT NULL,
    [SCHEMA_VER_REMARK] [varchar](250) NULL
);

some sample data (seems problem with sqlfiddle.. so putting some sample data):

```
INSERT INTO AUDIT_SCHEMA_VERSION
VALUES(1,6,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO AUDIT_SCHEMA_VERSION
VALUES(1,6,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO AUDIT_SCHEMA_VERSION
VALUES(1,7,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO AUDIT_SCHEMA_VERSION
VALUES(1,10,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO AUDIT_SCHEMA_VERSION
VALUES(1,12,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO AUDIT_SCHEMA_VERSION
VALUES(1,12,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO AUDIT_SCHEMA_VERSION
VALUES(1,16,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO AUDIT_SCHEMA_VERSION
VALUES(1,1

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+'_c

StackExchange Database Administrators Q#100899, answer score: 20