It has been a couple of month since i write any article on this blog. Well, Today I have come up with the very interesting requirement and will see that how we can achieve the result in sql server. By-The-Way this article’s title might be looking confusing for some readers, so i would recommend them to read the whole article.
Let’s move ahead. For one of my project, I have been given a requirement where my client wanted to display column name along with its value as row and generate custom column header to display for each entity. Hmm still confusing!! Don’t worry. Consider I have one table let say – Book which have different columns and contains data as shown in following image.
Now my requirement is to produce the result like following –
Cool. Hmm I can smell of pivoting of sql server. Well, if you think this then you are in the correct direction. But that’s not enough to achieve the result. Let’s see this practically by slicing out the result and see it step-by-step.
First i want to generate custom header like – “Book 1”, “Book 2” and so on as shown above. We can achieve it by using sql server’s ranking function – ROW_NUMBER. See this sql script –
DECLARE @DisplayCol NVARCHAR(MAX) = '' SELECT @DisplayCol += N', [Book- ' + CONVERT(NVARCHAR(500), ROW_NUMBER() OVER(ORDER BY BookId)) + ']' FROM dbo.Book SET @DisplayCol = STUFF(@DisplayCol, 1, 2, '') PRINT @DisplayCol
which produces the following output.
[Book- 1], [Book- 2], [Book- 3], [Book- 4]
Well, There might be other better way to get this type of result. Now next step is to display column name along with its value as row. To achieve this we’ll use “Table Value Constructor” t-sql syntax. Well, “Table Value Constructor” is also known as “Row Constructor”, a new feature introduced in sql server 2008 which allows multiple rows of data to be specified in a single statement using “VALUES” clause.
SELECT BookId, tab.* FROM dbo.Book CROSS APPLY ( VALUES ('BookTitle', BookTitle), ('Publisher', Publisher), ('Author', Author), ('Number Of Pages', CONCAT(NoOfPages, ' Page(s)')), ('Language', Language), ('Publication Date', CONVERT(VARCHAR(10), PublicationDate,101)) ) tab(ColKey, ColValue)
which produces the following output.
Now it’s time to produce the final output and for that we need to use a dynamic pivot query (as there can be “n” number of rows in the table) which will allow us to build the columns based on the type values. Now if we combine everything mentioned above then final query will looks like this –
DECLARE @SqlQuery NVARCHAR(MAX) = '', @ColumnToPivot NVARCHAR(MAX) = '', @DisplayCol NVARCHAR(MAX) = '' SELECT @DisplayCol += N', ' + QUOTENAME(BookId) + ' AS [Book-' + CONVERT(NVARCHAR(500), ROW_NUMBER() OVER(ORDER BY BookId)) + ']' FROM dbo.Book WITH (NOLOCK) SET @DisplayCol = STUFF(@DisplayCol, 1, 2, '') SELECT @ColumnToPivot += N', ' + QUOTENAME(BookId) FROM dbo.Book WITH (NOLOCK) SET @ColumnToPivot = STUFF(@ColumnToPivot, 1, 2, '') SET @SqlQuery = N' SELECT ColKey AS Attributes, ' + @DisplayCol + ' FROM ( SELECT BookId, tab.* FROM dbo.Book CROSS APPLY ( VALUES (''BookTitle'', BookTitle, 1), (''Publisher'', Publisher, 2), (''Author'', Author, 3), (''Price'', CONVERT(VARCHAR(10), Price), 4), (''Number Of Pages'', CONCAT(NoOfPages, '' Page(s)''), 5), (''Language'', Language, 6), (''Publication Date'', CONVERT(VARCHAR(10), PublicationDate,101), 7) ) tab(ColKey, ColValue, OrderVal) ) res PIVOT (MAX(ColValue) FOR BookId IN (' + @ColumnToPivot + ') ) pv' --PRINT @SqlQuery EXEC SP_EXECUTESQL @SqlQuery
Here i have created Sql Fiddle to play with this query. Enjoy.
That’s it. Hope you like this blog post. Feel free to give your feedback or query in comment section. Thank you for reading.