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: