CODEWITHSUNDEEP
sql-serversql-server-2008pivot-table
Sabyasachi Mishra
Sabyasachi Mishra

Reputation: 1749

SQL Server :Dynamic SQL with Pivot on the basis of maximum count

I have this SQL Server query:

SELECT [VendorID], [QuotedAmount]
FROM [tbl_Vendor_Quotation] 
WHERE [ProductID] = 1 
ORDER BY vendorID

This query returns data like this:

VendorID     QuotedAmount
-------------------------
   1            1000000
   1             900000
   1             750000
   1             720000
   1             650000
   2            1250000
   2            1200000
   3            1500000
   4            1000000
   4             970000
   4             950000
   5            1450000
   6            1450000
   8            1200000

I want to pivot this such a way that it will come up as per the below table

 VendorId        R0       R1        R2       R3        R4
 ----------------------------------------------------------
    1         1000000    900000   750000   720000    650000
    2         1250000   1200000
    3         1500000
    4         1000000    970000   950000
    5         1450000
    6         1450000
    8         1200000

R0, R1---Rn Shows Maximum times VendorID Repeated with QuotedAmount.In this case R0,R1 ---R4(5 times) as VendorID 1 is maximum time repeated 5 times.

I followed a lot of example to make it but didn't get successes. Please help me.

Upvotes: 0

Views: 36

Answers (3)

Sabyasachi Mishra
Sabyasachi Mishra

Reputation: 1749

Thanks @vercelli

I got my answer with this query with your help.

Declare @ProdID int
set @ProdID=1
DECLARE @columnName VARCHAR(1000)   
DECLARE @columnVal VARCHAR(1000) 
DECLARE @Query VARCHAR(1000) 

 --Create dynamic column name 
 SELECT @columnName =COALESCE(@columnName + ', ','')+'['+cast(rn as varchar(10))+'] AS [R'+cast(rn as varchar(10))+']',
  @columnVal =COALESCE(@columnVal + ',','')+'['+cast(rn as varchar(10))+']' FROM        
 ( select distinct  
               ROW_NUMBER () over (partition By VendorId order by QuotedAmount desc) as rn
         from tbl_Vendor_Quotation where [ProductRequestID]=@ProdID  ) a

     print  @columnName    print  @columnVal

  set @Query='select VendorId,'+@columnName+'
   from (
        select VendorId, QuotedAmount, 
               ROW_NUMBER () over (partition By VendorId order by QuotedAmount desc) as rn
         from tbl_Vendor_Quotation where [ProductRequestID]='+cast(@ProdID as varchar(10))+') t1
  PIVOT
  (
    max(QuotedAmount)
    for rn in ('+@columnVal+')) As PivotTable'
    exec (@Query)

Upvotes: 1

gofr1
gofr1

Reputation: 15987

Dynamic SQL + pivoting:

DECLARE @cols nvarchar(max),
        @sql nvarchar(max)

SELECT @cols = (
    SELECT DISTINCT ',[R'+  CAST(ROW_NUMBER() OVER (PARTITION BY VendorID ORDER BY (SELECT NULL)) - 1 as nvarchar(10)) +']'
    FROM tbl_Vendor_Quotation
    FOR XML PATH('')
    )

SELECT @sql = N'
SELECT *
FROM (
    SELECT  VendorID,
            QuotedAmount,
            ''R''+  CAST(ROW_NUMBER() OVER (PARTITION BY VendorID ORDER BY (SELECT NULL)) - 1 as nvarchar(10)) as Cols
    FROM tbl_Vendor_Quotation
    WHERE [ProductID] = 1 
    ) as p
PIVOT (
    MAX(QuotedAmount) FOR Cols IN ('+STUFF(@cols,1,1,'')+')
) as pvt'

EXEC sp_executesql @sql

Output:

VendorID    R0      R1      R2      R3      R4
1           1000000 900000  750000  720000  650000
2           1250000 1200000 NULL    NULL    NULL
3           1500000 NULL    NULL    NULL    NULL
4           1000000 970000  950000  NULL    NULL
5           1450000 NULL    NULL    NULL    NULL
6           1450000 NULL    NULL    NULL    NULL
8           1200000 NULL    NULL    NULL    NULL

The Rn number will grow dynamically in case when row number for vendor will grow.

Upvotes: 1

vercelli
vercelli

Reputation: 4757

You have to Rank them first with row_number window function:

 select VendorId, [1] as R0,[2] as R1,[3] as R2,[4] as R3,[5] as R4
   from (
        select VendorId, QuotedAmount, 
               ROW_NUMBER () over (partition By VendorId order by QuotedAmount desc) as rn
         from tbl_Vendor_Quotation
        WHERE [ProductID] = 1) t1
  PIVOT
  (
    max(QuotedAmount)
    for rn in ([1],[2],[3],[4],[5])) As PivotTable

Upvotes: 1

Related Questions