Thursday, January 29, 2015

Group and pivot data in SQL

Given below is an example of how to group and pivot data in SQL .This example shows you how to group data in CSV format and pivot it.

--Create temp table
CREATE TABLE  #car_data (company varchar(10), model varchar(20))

--Insert data
INSERT INTO #car_data (company,model) values ('Ford','Escape')
INSERT INTO #car_data (company,model) values ('Ford','Explorer')
INSERT INTO #car_data (company,model) values ('Ford','Expedition')

INSERT INTO #car_data (company,model) values ('Chevy','Cruze')
INSERT INTO #car_data (company,model) values ('Chevy','Impala')
INSERT INTO #car_data (company,model) values ('Chevy','Silverado')

-- Group data in CSV format
SELECT  t.company
    ,STUFF((SELECT ', ' + model
        FROM #car_data with(nolock)
        WHERE company = t.company
        FOR XML PATH(''), TYPE)
    .value('.','VARCHAR(400)'),1,2,' ') List_Output
FROM #car_data t with(nolock)
GROUP BY t.company 


1 comment:

Suseela said...


What an awesome post, I just read it from start to end. Learned something new after a long time.



Cloud Computing training in Chennai

Salesforce training in Chennai

VMware training in Chennai

AWS training in Chennai