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