"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

June 09, 2012

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
Happy Learning!!!

No comments: