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:

	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(1,6,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(1,6,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(1,7,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(1,10,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(1,12,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(1,12,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(1,16,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(1,16,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(1,16,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(1,16,13,CAST('20140417 18:10:44.100' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(2,5,0,CAST('20140417 18:14:14.157' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(2,6,0,CAST('20140417 18:14:23.327' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(2,7,0,CAST('20140417 18:14:32.270' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(2,8,0,CAST('20141209 09:38:40.700' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(2,9,0,CAST('20141209 09:43:04.237' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(2,10,0,CAST('20141209 09:45:19.893' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(2,13,0,CAST('20150323 14:54:30.847' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(1,10,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(1,16,14,CAST('20140417 18:11:07.977' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(1,16,15,CAST('20140417 18:11:13.130' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(2,2,0,CAST('20140417 18:12:11.200' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(2,3,0,CAST('20140417 18:12:33.330' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(2,4,0,CAST('20140417 18:12:48.803' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(1,13,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(1,16,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(2,11,0,CAST('20141209 09:45:58.993' as DATETIME),'Stored procedure build')
	INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
	VALUES(2,12,0,CAST('20141209 09:46:50.070' as DATETIME),'Stored procedure build');

Here is the [SQLFiddle](http://sqlfiddle.com/#!6/9f84d) with sample data.

Can someone with T-SQL expertise guide me on how to achieve the final result? I know that `PIVOT` (with dynamic columns) will be the right approach, but cant figure it out.

Expected results :

![enter image description here][1]

So far, I have below :

    select row_number() over (
    		partition by CONVERT(varchar(10), SCHEMA_VER_DATE, 110) order by SCHEMA_VER_DATE 
    		) as rownum
    	,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'
    order by UPGRADE_DATE 

![enter image description here][2]


  [1]: https://i.stack.imgur.com/IvorG.png
  [2]: https://i.stack.imgur.com/TOybF.png
Top Answer
taryn (imported from SE)
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][1]. 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][2]. 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][3]. 

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][4]. 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 |
            


  [1]: http://sqlfiddle.com/#!6/9f84d/16
  [2]: http://sqlfiddle.com/#!6/9f84d/32
  [3]: http://sqlfiddle.com/#!6/9f84d/33
  [4]: http://sqlfiddle.com/#!6/9f84d/34
Answer #2
JoseTeixeira (imported from SE)
Adding explanation and a fiddle: http://sqlfiddle.com/#!6/c92b2/5.

The query below:
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 |

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.