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

Question

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.

score 1 · Answer 1 · answered Aug 18 '16 at 09:00

1

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

answered Aug 18 '16 at 09:00

vercelli

4,717
2
13
15

It works fine.But what if 1 will repeated another time? R5 will automatically come ? – Sabyasachi Mishra Aug 18 '16 at 09:05
@SabyasachiMishra No, it's not dynamic. You said "maximum time repeated 5 times". – vercelli Aug 18 '16 at 09:06
1

@SabyasachiMishra if you are looking for dynamic pivot try this: http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query (with the ranking I used) – vercelli Aug 18 '16 at 09:11
Here it is 5 time as `VendorID` 1 is repeated 5 times.It can of of n times depending upon a `QuotedAmount` repetition for a particular `VendorId `. – Sabyasachi Mishra Aug 18 '16 at 09:13

score 1 · Accepted Answer · answered Aug 18 '16 at 09:43

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.

It gives perfect O/P but with 2 extra column with null value. — Sabyasachi Mishra, Aug 18 '16 at 10:01
Maybe there are some Vendors with `NULL`s in `QuotedAmount`? Please, check this. — gofr1, Aug 18 '16 at 10:04

score 1 · Answer 3 · answered Aug 18 '16 at 09:56

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)

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

3 Answers3