CODEWITHSUNDEEP
sqlsql-server
Prashant Pimpale
Prashant Pimpale

Reputation: 10707

How to add columns dynamically in the PIVOT query

I have a #temp table in that the data is like [This is not the actual data but similar to this], I have filled that data by using Dynamic PIVOT

Name      Department  Div   Science Maths  Bio
Prashant    CSE        A    20      NULL   NULL
Prashant    CSE        A    NULL    50     NULL
Lisa       CBSE        A    11      NULL   NULL
Lisa       CBSE        A    NULL    90     NULL
Lisa       CBSE        A    NULL    NULL   56

So what I want is,

Name      Department    Div Science Maths   Bio
Prashant    CSE         A   20      50     NULL
Lisa       CBSE         A   12      90     56

The dynamic SQL what I have used [Actual Query which produce an output like first table:

SET @DynamicPivotQuery = 
    N'SELECT departmentName as Department,EmployeeCode as EmpCode,
    designationName as Designation,employeeName as EmployeeName,NatureofActivity,
       oversightCode,'+@selectCols+'
    INTO ##TempPivot
    FROM #OVERSIGHTANDCOETABLE
    PIVOT(SUM(percentage) 
          FOR name IN (' + @cols + ')) AS PVTTable PIVOT
          (
          MAX(OversightFunction) 
          FOR name1 IN (' + @displayCols + ')) AS PVTTable1'

Upvotes: 0

Views: 63

Answers (3)

sai potnuru
sai potnuru

Reputation: 1

create table temp
(
    date datetime,
    category varchar(3),
    amount money
)

insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('3/1/2012', 'ABC', 1100.00)


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT date, ' + @cols + ' from 
            (
                select date
                    , amount
                    , category
                from temp
           ) x
            pivot 
            (
                 max(amount)
                for category in (' + @cols + ')
            ) p '

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522471

Try this option:

WITH cte AS (
    -- your current dynamic query
)

SELECT
    Name,
    Department, 
    Div,
    MAX(Science) AS Science,
    MAX(Maths) AS Maths,
    MAX(Bio) AS Bio
FROM cte
GROUP BY
    Name,
    Department, 
    Div;

Upvotes: 2

Chanukya
Chanukya

Reputation: 5893

You can use aggregate functions to get required output 

 select Name,max(Department), max(Div), max(Science),max( Maths) ,max(  Bio)
    from table
    group by Name

Upvotes: 1

Related Questions