TSQL Tip for a Day

Today's post is learning from my question. Below is sample example

 

Step 1 - Create Table and Load Sample Data 

CREATE TABLE TestC

(Comments Char(100),)
INSERT INTO TestC VALUES('A'),('B'),('C'),('D'),('E'),('F'),('G')SELECT * FROM TestC 

Step 2 - Pivot Query

DECLARE @Query VARCHAR(MAX)

DECLARE @cols VARCHAR(MAX)
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(Comments) 
            FROM [dbo].[TestC]
            FOR XML PATH(''), TYPE
           ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

 

SET @Query = '

SELECT * from ( SELECT *
    FROM TestC ) AS P 
PIVOT( MIN(Comments) FOR Comments in ('+ @cols + ') ) pvt'
 EXEC(@Query)    

 

Table Entries

 

Query Result

 

Couple of Interesting TSQL questions and answers from dba stackexchange site.

What is the difference between select count(*) and select count(any_non_null_column)?

• COUNT(*) will include NULLS
• COUNT(column_or_expression) won't.

Next Set of Questions List

• SQL query to convert columns into rows
• Generating large strings for test data
• How to select the first row of each group?
• SQL query that concatenate values from duplicate rows in a single table
• Select multiple rows from one row based on column values

Happy Learning!!!