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
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
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 |