-1

I would like to get what I have shown in the desired results from the sample tables provided.

I have been struggling with creating the SQL to handle this:

CREATE TABLE a (account nvarchar(10), accountname nvarchar(10));
CREATE TABLE b (bid int, bdesc nvarchar(10));
CREATE TABLE C (cid int, bid int, caccount nvarchar(10));

INSERT INTO a VALUES ('a1','a1val');
INSERT INTO a VALUES ('b1','b1val');
INSERT INTO a VALUES ('c1','c1val');
INSERT INTO a VALUES ('d1','d1val');

INSERT INTO b VALUES (1,'val1');
INSERT INTO b VALUES (2,'val2');
INSERT INTO b VALUES (3,'val3');

INSERT INTO c VALUES (1,1,'a1');
INSERT INTO c VALUES (2,1,'b1');
INSERT INTO c VALUES (3,2,'a1');
INSERT INTO c VALUES (4,3,'c1');

--Desired results
--accountname  val1  val2  val3
--a1val        x     x  
--b1val        x
--c1val                    x
--d1val

select * from a
select * from b
select * from c 

drop table a, b, c
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3301576
  • 1
  • 1

1 Answers1

0

This solution will work for any number of distinct values in bdesc.

declare @dynamicSQL varchar(max) = ''
declare @columnList varchar(max) = ''
declare @unique table ([bdesc] varchar(10) )
insert into @unique
select distinct [bdesc] from b 
select @columnList = @columnList + '[' + bdesc + '],'  from @unique
set @columnList = SUBSTRING(@columnList, 0, LEN(@columnList))
set @dynamicSQL = '' +
'select *
from
(
select accountname, bdesc
from a as a
left join c as c
on a.account = c.caccount
left join b as b
on c.bid = b.bid
) as src
pivot(
max(bdesc)
for bdesc in (' + @columnList + ')' + '
) as pvt'
exec(@dynamicSQL)
Erran Morad
  • 4,563
  • 10
  • 43
  • 72