Convert a column values to fields and retain other values in sql server

Question

Can someone help me in converting the below mentioned original table to table required? I think I have done it before, it's just I am unable to do it now. Thanks for the help.

Original Table              
year    school  program count   
2014     A      XYZ     3   
2014     A      DEF     1   
2014     B      XYZ     2   
2014     B      DEF     4   
2014     B      GHI     5   
2014     C      XYZ     3   

Table Required              
YEAR    SCHOOL  XYZ DEF GHI
2014     A      3   1   0
2014     B      2   4   5
2014     C      3   0   0

http://stackoverflow.com/questions/24470/sql-server-pivot-examples — Amir Pelled, Oct 27 '15 at 09:09

score 1 · Answer 1 · answered Oct 27 '15 at 09:10

1

You can try using this query. It first computes a temporary table containing the XYZ, DEF, and GHI count for each record. Then the outer query aggregates these counts for each year/school combination.

SELECT t.year, t.school,
    SUM(t.XYZ) AS XYZ, SUM(t.DEF) AS DEF, SUM(t.GHI) AS GHI
FROM
(
    SELECT year, school,
        CASE WHEN program = 'XYZ' THEN count ELSE 0 END AS XYZ
        CASE WHEN program = 'DEF' THEN count ELSE 0 END AS DEF
        CASE WHEN program = 'GHI' THEN count ELSE 0 END AS GHI
    FROM table
) t
GROUP BY t.year, t.school

answered Oct 27 '15 at 09:10

Tim Biegeleisen

502,043
27
286
360

Thanks @Tim Biegeleisen – sr_coder Oct 28 '15 at 05:30
Please mark the "best" answer correct (whatever you feel it is). – Tim Biegeleisen Oct 28 '15 at 05:31

score 1 · Accepted Answer · answered Oct 27 '15 at 09:47

Try Dynamic Pivot,

CREATE TABLE #Your_Table
(
    YEAR INT,
    SCHOOL CHAR(1),
    PROGRAM VARCHAR(10),
    COUNT INT
)

INSERT INTO #Your_Table
VALUES      (2014,'A','XYZ',3),
            (2014,'A','DEF',1),
            (2014,'B','XYZ',2),
            (2014,'B','DEF',4),
            (2014,'B','GHI',5),
            (2014,'C','XYZ',3) 

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @TempColumnname AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName = ISNULL(@ColumnName + ',', '')
                     + Quotename(PROGRAM)
FROM   (SELECT DISTINCT PROGRAM
        FROM   #Your_Table) AS Courses

SELECT @TempColumnname = ISNULL(@TempColumnname + ',', '')
                         + 'ISNULL(' + Quotename(PROGRAM) + ',0) AS '+Quotename(PROGRAM)
FROM   (SELECT DISTINCT PROGRAM
        FROM   #Your_Table) AS Courses
--PRINT @TempColumnname

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = N'SELECT Year, School, ' + @TempColumnname
                         + 'FROM #Your_Table PIVOT(SUM(Count) 
          FOR PROGRAM IN (' + @ColumnName + ')) AS PVTTable'

--Execute the Dynamic Pivot Query
EXEC SP_EXECUTESQL
  @DynamicPivotQuery

score 0 · Answer 3 · answered Oct 27 '15 at 09:27

Use PIVOT:

SELECT
  YEAR, 
  SCHOOL, 
  COALESCE([XYZ], 0) [XYZ], 
  COALESCE([DEF], 0) [DEF], 
  COALESCE([GHI], 0) [GHI]
INTO
  Table_Required
FROM
  Original_table
PIVOT
  (SUM([count])  
FOR program
  in([XYZ],[DEF],[GHI])  
  )AS p
ORDER BY YEAR, SCHOOL

Convert a column values to fields and retain other values in sql server

3 Answers3