Multiple row value as Column value

Transact-SQL
1

Hi,

I have a table like below

CREATE TABLE #Table (Code varchar(50),Qty Float,SupName VARCHAR(10),SupQty float,SupPrice decimal(18,3))

insert into #Table VALUES ('Code1',10,'Supplier1',10,20)
insert into #Table VALUES ('Code1',10,'Supplier2',6,25)
insert into #Table VALUES ('Code2',10,'Supplier1',10,15)
insert into #Table VALUES ('Code3',10,'Supplier1',10,30)
insert into #Table VALUES ('Code3',10,'Supplier2',10,28)
select * from #Table

drop Table #Table

I need the result as below script

CREATE TABLE #Result (Code varchar(50),Qty Float,Supplier1Qty float,Supplier1Price decimal(18,3),Supplier2Qty float,Supplier2Price decimal(18,3),LowestSup varchar(10),
LowestSupQty float,LowestSupPrice decimal(18,3))
insert into #Result VALUES ('Code1',10,10,20,6,25,'Supplier1',10,20)
insert into #Result VALUES ('Code2',10,10,15,NULL,NULL,'Supplier1',10,15)
insert into #Result VALUES ('Code3',10,10,30,10,28,'Supplier2',10,28)
select * from #Result

drop Table #Result
Supplier name should be as column. supplier name is not static column, it will dynamically increase/decrease. the last three column states that the lowest in the values.

Regards

Kasim

2

select
main.code
,main.qty
,supplier1qty
,supplier1price
,supplier2qty
,supplier2price
,lowestsup
,lowestsupqty
,lowestsupprice
from (
select distinct code,qty from #table
)main
left join (
select code ,supplier1 as supplier1qty,supplier2 as supplier2qty from (
select code,supname ,supqty from #table)t
pivot (sum(supqty) for supname in ([supplier1],[supplier2]))t
)qty
on main.code = qty.code
left join (
select code ,supplier1 as supplier1price ,supplier2 as supplier2price from (
select code,supname ,supprice from #table)th
pivot (sum(supprice) for supname in ([supplier1],[supplier2]))t
) price
on main.code = price.code
left join (
select code ,supname as lowestsup,supqty as lowestsupqty,supprice as lowestsupprice from #table t
where exists( select * from (select code,min(supprice) as lowestsupqty from #table group by code) m
where t.code = m.code and t.supprice = m.lowestsupqty )
)low
on main.code = low.code

3

For dynamic Sql

Declare @SQL varchar(max)
declare @Supplier varchar(max)

set @Supplier =
stuff(
(
Select ','+quotename(y) as [text()] from (
select distinct SupName as y
from #Table) m
order by y
for xml path('')
),1,1,'')

set @SQL =

'select
main.code
,main.qty
,supplier1qty
,supplier1price
,supplier2qty
,supplier2price
,lowestsup
,lowestsupqty
,lowestsupprice
from (
select distinct code,qty from #table
)main
left join (
select code ,supplier1 as supplier1qty,supplier2 as supplier2qty from (
select code,supname ,supqty from #table)t
pivot (sum(supqty) for supname in ('+@Supplier+'))t
)qty
on main.code = qty.code
left join (
select code ,supplier1 as supplier1price ,supplier2 as supplier2price from (
select code,supname ,supprice from #table)th
pivot (sum(supprice) for supname in ('+@Supplier+'))t
) price
on main.code = price.code
left join (
select code ,supname as lowestsup,supqty as lowestsupqty,supprice as lowestsupprice from #table t
where exists( select * from (select code,min(supprice) as lowestsupqty from #table group by code) m
where t.code = m.code and t.supprice = m.lowestsupqty )
)low
on main.code = low.code'

--Print @SQL
exec (@SQL)

4

Not sure I completely understand the question, but here's my suggestion:

with cte
  as (select code
            ,qty
            ,supqty
            ,supprice
            ,supname
            ,row_number() over(partition by code order by supprice) as rn
        from #table
     )
select a.code
      ,a.qty
      ,a.supqty as supplier1qty
      ,a.supprice as supplier1price
      ,b.supqty as supplier2qty
      ,b.supprice as supplier2price
      ,a.supname as lowestsup
      ,a.qty as lowestsupqty
      ,a.supprice as lowestsupprice
  from cte as a
       left outer join cte as b
                    on b.code=a.code
                   and b.rn=2
 where a.rn=1
;
5

If there are three suppliers do you want an additional column to be added?

If so:

Is this just a report? Maybe you could use PIVOT?

If not just a report then it would be better to have a child-table (of Suppliers and Prices for Product codes, rather than a table with one-column-per-supplier).

Sorry if I have misunderstood your question