CODEWITHSUNDEEP
sql-serverdatabaset-sqljoinpivot-table
user4833581
user4833581

Reputation: 131

I want to convert some data to pivot in SQL Server with join and dynamic

enter image description here

The table, at last, is my target.

This is my demo database 

create database pvtestDb;
go

use pvtestDb;
go

create table custTransaction
(
    id int,
    custNum int,
    value nvarchar(50)
)
go

create table customers
(
    id int,
    custName nvarchar(50)
)

insert into Customers(id, custName) 
values (1, 'aaa'), (2, 'bbb'), (3, 'ccc'), (4, 'ddd'), 
       (5, 'eee'), (6, 'fff'), (7, 'ggg'), (8, 'hhh'), (9, 'iii')

insert into custTransaction (id, custNum, value) 
values (1, 3, 'a'), (1, 4, 'b'), (1, 5, 'c'),
       (2, 3, 'd'), (2, 4, 'e'), (2, 6, 'f'),
       (3, 3, 'g'), (3, 8, 'h'), (3, 9, 'i')

select * from customers
select * from custTransaction


select custName, custNum, value 
from customers
join custTransaction on custTransaction.id = customers.id

I tried code like this but not worked at all 

SELECT 
    custNum, [a], [b], [c], [d]  
FROM
    customers
JOIN
    custTransaction ON custTransaction.id = customers.id
PIVOT
    (COUNT([custName]) 
     FOR [custName] IN ([a], [b], [c], [d])) AS p

I need to join between the two tables in first.

Any hints would be appreciated as I am stuck with this situation

Upvotes: 0

Views: 37

Answers (1)

uzi
uzi

Reputation: 4146

Here's approach with dynamic SQL

declare @customers varchar(8000)
declare @sql varchar(8000)

select @customers = stuff((
    select ',' + quotename(custName)
    from customers
    for xml path('')
), 1, 1, '')

set @sql = 'select
        id, ' + @customers + '
    from (
        select
            ct.id, c.custName, ct.value
        from 
            customers c
            join custTransaction ct on ct.custNum = c.id
    ) t
    pivot (
        max(value) for custName in (' + @customers + ')
    ) p'

exec (@sql)

Output

id  aaa     bbb     ccc   ddd   eee   fff   ggg     hhh     iii
----------------------------------------------------------------
1   NULL    NULL    a     b     c     NULL  NULL    NULL    NULL
2   NULL    NULL    d     e     NULL  f     NULL    NULL    NULL
3   NULL    NULL    g     NULL  NULL  NULL  NULL    h       i

Upvotes: 2

Related Questions