CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
soniya
soniya

Reputation: 33

Pivot with all data only column change

I need to convert SQL table into expected o/p.

Current table

id   status month
100  P      August
101  D      August
101  P      August
102  P      August
102  P      sept

expected o/p:

id   August Sept
100  P      NULL 
101  D      NULL
101  P      NULL
102  P      P

I need all records.

Upvotes: 3

Views: 40

Answers (3)

shA.t
shA.t

Reputation: 16968

You can use Dynamic SQL if your [month]s are dynamic like this:

declare @sql nvarchar(max)= '';

-- gathering column names in @sql
select @sql = @sql + ',[' + [month] + ']'
from yourTable
group by [month];

-- creating query text with gathered name of columns
set @sql = 'select id'+@sql+' from (select *, rank() over (partition by id,[month] order by id,[status])rnk from yourTable) w pivot(max([status]) for [month] in ('+
    stuff(@sql,1,1,'')+')) as pvt';

-- executing query
EXEC(@sql);

Another way to use XQuery is like this to achieve it:

;with tx as (
    select id, 
        -- generate an xml field with `for xml`
        cast((select 
                -- using `case` to separate columns per value of `[mont]`
                case when [month] = 'August' then ti.[status] end [August],
                case when [month] = 'sept' then ti.[status] end [sept]
            from t ti       
            where t.id = ti.id      
            for xml path('')) as xml) x
    from yourTable
    group by id
)
select id, 
    -- get value of node
    txAugust.c.value('.', 'varchar(10)') [August], 
    txsept.c.value('.', 'varchar(10)') [sept]
from tx
-- using nodes of `[August]` 
outer apply
tx.x.nodes('./August') txAugust(c)
-- using nodes of `[sept]`
outer apply
tx.x.nodes('./sept') txsept(c);

Upvotes: 0

Serkan Arslan
Serkan Arslan

Reputation: 13393

Another solution.

SELECT *  FROM
    (SELECT CONCAT(id,  status) UNQ, * FROM @MyTable) SRC
    PIVOT ( MAX(status) FOR [month] IN ([August],[Sept])) PVT

Result:

id          August     Sept
----------- ---------- ----------
100         P          NULL
101         D          NULL
101         P          NULL
102         P          P

Upvotes: 0

Mr. Bhosale
Mr. Bhosale

Reputation: 3106

Use Pivot.

select *  FROM (
SELECT id  , status, month,
ROW_NUMBER() over( partition by id,month order by id,status )rnk
FROM #tableName w 

) up
PIVOT (  max(status) FOR month IN ([August],[Sept])) AS pvt

Upvotes: 1

Related Questions