SQL Pivot Query assistance

Question

I have a table

+----+-------+----------+-------+--------------+
| ID | REQID |  VENDOR  | QUOTE | DELIVERYDATE |
+----+-------+----------+-------+--------------+
|  1 | R1    | Vendor_1 |    99 | 2014-06-15   |
|  2 | R1    | Vendor_2 |    88 | 2014-07-15   |
|  3 | R1    | Vendor_3 |    77 | 2014-08-15   |
|  4 | R2    | Vendor_4 |    66 | 2014-09-15   |
+----+-------+----------+-------+--------------+

SQL Fiddle here

I need the query to return three columns with the Vendor as the Pivot point. So my rows would look like so:

+-----------+-----------+-----------+
| Vendor_1  | Vendor_2  | Vendor_3  |
+-----------+-----------+-----------+
| 99        | 88        | 77        |
| 2014-6-15 | 2014-7-15 | 2014-8-15 |
+-----------+-----------+-----------+

The cte returns the correct records, I just don't know how to write the Pivot part or if it is even possible to return what I need.

Answer 1

You didn't specify what version of SQL Server you are using but you can get the final result by unpivoting your date and quote columns first, then pivot the data. The basic syntax will be similar to:

select Vendor_1, Vendor_2, Vendor_3
from
(
   select vendor, col, value,
     row_number() over(partition by vendor
                       order by vendor) seq
   from vendorquotes
   cross apply
   (
     values
      ('Quote', cast(quote as varchar(10))),
      ('DeliveryDate', convert(varchar(10), deliverydate, 120))
   ) c(col, value)
) d
pivot
( 
  max(value)
  for vendor in (Vendor_1, Vendor_2, Vendor_3)
) pi;

See SQL Fiddle with demo

The critical thing with this is you need to use a windowing function like row_number so you can return multiple rows for each of the vendors.

If you have an unknown number of Vendors, then you will need to use dynamic SQL to get this result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Vendor) 
                    from VendorQuotes
                    group by Vendor
                    order by Vendor
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' 
            from 
            (
               select vendor, col, value,
                 row_number() over(partition by vendor
                                   order by vendor) seq
               from vendorquotes
               cross apply
               (
                 values
                  (''Quote'', cast(quote as varchar(10))),
                  (''DeliveryDate'', convert(varchar(10), deliverydate, 120))
               ) c(col, value)
            ) x
            pivot 
            (
                max(value)
                for Vendor in (' + @cols + ')
            ) p '

exec sp_executesql @query;

See SQL Fiddle with Demo

Finally, if you want to pass in the ReqID as a parameter, then you can alter the dynamic SQL to be:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @ReqID varchar(20),
    @ParmDefinition NVARCHAR(500)


SET @ParmDefinition = '@ReqID varchar(20)'
SET @ReqID = 'R1'

select @cols = STUFF((SELECT ',' + QUOTENAME(Vendor) 
                    from VendorQuotes
                    where Reqid = @ReqID
                    group by Vendor
                    order by Vendor
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' 
            from 
            (
               select vendor, col, value,
                 row_number() over(partition by vendor
                                   order by vendor) seq
               from vendorquotes
               cross apply
               (
                 values
                  (''Quote'', cast(quote as varchar(10))),
                  (''DeliveryDate'', convert(varchar(10), deliverydate, 120))
               ) c(col, value)
               where ReqID = @ReqID
            ) x
            pivot 
            (
                max(value)
                for Vendor in (' + @cols + ')
            ) p '

exec sp_executesql @query, @ParmDefinition, @ReqID = @ReqID;

See SQL Fiddle with Demo

Answer 2

To be honest (if I understand your question right) I think you should think this over again. I would do this as follows:

Vendor_name     Value     Date
Vendor_1        99        2014-6-15
Vendor_2        88        2014-7-15
Vendor_3        77        2014-8-15

This way you have all related data in one row.

If you put relevant data in columns you will need to add more columns if more vendors are added and you need to be sure all your related data input is in the same order all the time.