Cross tab query

Transact-SQL
1

Hi,
I have data as below ,
Prod.id. supplier. Price
Tea. Adolf. 10000
Tea. Bedoft. 5000
Tea. Cedoft. 2000

I want to priviewing the data so can resulting as below

Prod.id. Supp.1. Price. 1 Supp.2. Price2 Supp3
Tea. Adoft 10.000. Bedoft. 5000. Cedoft

Please help or clues

Thx.

Joe

3

Hi

I tried something .. i hard coded some stuff ..
i think this is not the FINAL sql

Please take a look .. maybe others watching can help

Drop Create Data 
use  tempdb 
go 


drop table #data 
go 


create table #data 
(
Prodid  varchar(100) ,
supplier  varchar(100) ,
Price  int 
)
go 

insert into #data select 'Tea','Adolf',10000
insert into #data select 'Tea','Bedoft', 5000
insert into #data select 'Tea','Cedoft', 2000
go
SQL 
;WITH cte 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY prodid, supplier) rn, 
                * 
         FROM   #data), 
     onecte 
     AS (SELECT * 
         FROM   cte 
         WHERE  rn = 1), 
     twocte 
     AS (SELECT * 
         FROM   cte 
         WHERE  rn = 2), 
     threecte 
     AS (SELECT * 
         FROM   cte 
         WHERE  rn = 3) 
SELECT a.prodid, 
       a.supplier, 
       a.price, 
       b.supplier, 
       b.price, 
       c.supplier, 
       c.price 
FROM   onecte a 
       JOIN twocte b 
         ON a.prodid = b.prodid 
       JOIN threecte c 
         ON a.prodid = c.prodid
Result

image
image.png1105×239 4.4 KB

4

Alternative:

with cte
  as (select prodid
            ,supplier
            ,price
            ,row_number() over(partition by prodid order by supplier) as rn
        from yourtable as a
     )
select prodid
      ,min(case when rn=1 then supplier else null end) as supp1
      ,min(case when rn=1 then price    else null end) as price1
      ,min(case when rn=2 then supplier else null end) as supp2
      ,min(case when rn=2 then price    else null end) as price2
      ,min(case when rn=3 then supplier else null end) as supp3
      ,min(case when rn=3 then price    else null end) as price3
  from cte
 group by prodid
;
5

great..thx

6

drop table #temp
create table #temp
(
RN int,
ProdID char(10),
Supplier varchar(50),
Price int
)

insert into #temp
Select row_number() over(partition by ProdID order by Supplier) as RN, ProdID,Supplier,Price
from suppliers

select distinct ProdID,(select Supplier from #temp where RN = 1) AS Supp1, (select Price from #temp where RN = 1) AS Price1,
(select Supplier from #temp where RN = 2) AS Supp2, (select Price from #temp where RN = 2) AS Price2,
(select Supplier from #temp where RN = 3) AS Supp3, (select Price from #temp where RN = 3) AS Price3 from #temp