CODEWITHSUNDEEP
sqlsql-serverpivot
DotNetGeek
DotNetGeek

Reputation: 165

Pivot/ Crosstab Query Help needed

I have a query like below which gives following result.

Query

select OrderID, AccessName, Address1, Postcode, GeneralInstructions, --CompanyName,
col, --col+'_'+cast(rn as varchar(10)) col, 
val
from 
(
   select o.OrderID, js.JobStatusID, p.Name, o.AccessName, o.Address1, o.Postcode,   oj.GeneralInstructions, s.CompanyName
  , row_number() over(partition by o.OrderID 
                      order by js.JobStatusID) rn
FROM 
NEPCCO.Orders o
inner join NEPCCO.Clients c on o.ClientID = c.ClientID
inner join NEPCCO.OrderJobs oj on o.OrderID = oj.OrderID
inner join NEPCCO.Suppliers s on oj.SupplierID = s.SupplierID
inner join NEPCCO.Products p on oj.ProductID = p.ProductID
inner join NEPCCO.OrderStatus os on o.OrderStatusID = os.OrderStatusID
inner join NEPCCO.JobStatus js on oj.JobStatusID = js.JobStatusID
where 
o.OrderID in (50211, 44621) and 
(p.ProductID in (35,36,37,38,38,40,41,42,43) or  p.ProductID in (33,34))
 ) d
 unpivot
 (
   val
   for col in (Name)
) un

First Output

OrderID     AccessName      Address1                PostCode   Col Val    
44621   Mr Hayden   7 Broad Rush Green  LU7 2XA    Name  FPA - 28
44621   Mr Hayden   7 Broad Rush Green  LU7 2XA    Name  AW EPR
50211   Mrs Godwin  64 Riverview    MK43 7PN       Name  FPA - 28

Another Query for same thing to get desired output

select *
from
(
   select OrderID, AccessName, Address1, Postcode, GeneralInstructions, --CompanyName,
    col, --col+'_'+cast(rn as varchar(10)) col, 
    val
  from 
  (
    select o.OrderID, js.JobStatusID, p.Name, o.AccessName, o.Address1, o.Postcode, oj.GeneralInstructions, s.CompanyName
     , row_number() over(partition by o.OrderID,js.JobStatusID 
                      order by o.OrderID) rn
   FROM 
   NEPCCO.Orders o
   inner join NEPCCO.Clients c on o.ClientID = c.ClientID
   inner join NEPCCO.OrderJobs oj on o.OrderID = oj.OrderID
   inner join NEPCCO.Suppliers s on oj.SupplierID = s.SupplierID
   inner join NEPCCO.Products p on oj.ProductID = p.ProductID
   inner join NEPCCO.OrderStatus os on o.OrderStatusID = os.OrderStatusID
   inner join NEPCCO.JobStatus js on oj.JobStatusID = js.JobStatusID
   where 
   o.OrderID in (50211, 44621) and 
   (p.ProductID in (35,36,37,38,38,40,41,42,43) or  p.ProductID in (33,34))
    ) d
   unpivot
  (
   val
   for col in (Name)
  ) un
  ) s
 pivot
 (
 max(val)
 for col in (Name,JobStatusID,  JobStatusID1, Name1)
) piv

Second Output which is near to desired one but not what I want

OrderID     AccessName      Address1            PostCode   Col              Val 
 44621  Mr Hayden   7 Broad Rush Green  LU7 2XA  FPA - 28   NULL     
 50211  Mrs Godwin  64 Riverview        MK43 7PN     FPA - 28   NULL

Desired Output - Check the Column header names, which I want to show, from first query

OrderID     AccessName      Address1           PostCode   InstallJobType  EPR Type   
 44621  Mr Hayden   7 Broad Rush Green LU7 2XA    FPA - 28        AW EPR
 50211  Mrs Godwin  64 Riverview    MK43 7PN      FPA - 28        NULL

Any help would be appreciated as I am stuck

DATA PRIOR TO UNPIVOT

OrderID     JobstatusId   Name      AccessName        Address1             PostCode    CompanyName         M

   44621    3         AW EPR    Mr Hayden   7 Broad Rush Green  LU7 2XA   N/A (Sole trader)                 1
   44621    14        FPA - 28  Mr Hayden   7 Broad Rush Green  LU7 2XA Just Energy Solutions Limited       2
   50211    17        FPA - 28  Mrs Godwin  64 Riverview        MK43 7PN    Just Energy Solutions Limited   1

EDIT - kindly check if I am doing it right as it gives my the desired output

select OrderID, AccessName, Address1, Postcode, GeneralInstructions, 
Name_1 as "Install Jobs", 
Name_2 as "EPR Jobs",
CompanyName,Name
from
(
  select OrderID, AccessName, Address1, Postcode, GeneralInstructions, CompanyName,
    col+'_'+cast(rn as varchar(10)) col, 
    val
  from 
  (
    select o.OrderID, js.JobStatusID, p.Name, o.AccessName, o.Address1, o.Postcode, oj.GeneralInstructions, s.CompanyName, 
    js.Name as "Install Job Status", js.JobStatusID as JS1
      , row_number() over(partition by o.OrderID 
                          order by o.OrderID) rn
    FROM 
    NEPCCO.Orders o
    inner join NEPCCO.Clients c on o.ClientID = c.ClientID
    inner join NEPCCO.OrderJobs oj on o.OrderID = oj.OrderID
    inner join NEPCCO.Suppliers s on oj.SupplierID = s.SupplierID
    inner join NEPCCO.Products p on oj.ProductID = p.ProductID
    inner join NEPCCO.OrderStatus os on o.OrderStatusID = os.OrderStatusID
    inner join NEPCCO.JobStatus js on oj.JobStatusID = js.JobStatusID
    where 
    o.OrderID in (50211, 44621) and 
    (p.ProductID in (35,36,37,38,38,40,41,42,43) or  p.ProductID in (33,34))
  ) d
  unpivot
  (
    val
    for col in (Name)
  ) un
) s
pivot
(
  max(val)
  for col in (JobStatusID_1, Name_1,  JobStatusID_2, Name_2,Name)
) piv

Upvotes: 2

Views: 88

Answers (1)

Taryn
Taryn

Reputation: 247720

Your current query is confusing because you are applying UNPIVOT to only the Name column. UNPIVOT is used to transform multiple columns into multiple rows. Based on the columns that you have in the PIVOT portion of your query, I am guessing that you need to UNPIVOT the name and the JobStatus columns.

If that is correct, then your query should be similar to the following:

select *
from
(
   select OrderID, AccessName, Address1, Postcode, 
    GeneralInstructions, --CompanyName,
    col+cast(rn as varchar(10)) col,
    val
  from 
  (
    select o.OrderID, 
      cast(js.JobStatusID as varchar(50)) JobStatusId, 
      cast(p.Name as varchar(50)) Name, 
      o.AccessName, o.Address1, o.Postcode, 
      oj.GeneralInstructions, s.CompanyName
     , row_number() over(partition by o.OrderID,js.JobStatusID 
                      order by o.OrderID) rn
     FROM NEPCCO.Orders o
     inner join NEPCCO.Clients c on o.ClientID = c.ClientID
     inner join NEPCCO.OrderJobs oj on o.OrderID = oj.OrderID
     inner join NEPCCO.Suppliers s on oj.SupplierID = s.SupplierID
     inner join NEPCCO.Products p on oj.ProductID = p.ProductID
     inner join NEPCCO.OrderStatus os on o.OrderStatusID = os.OrderStatusID
     inner join NEPCCO.JobStatus js on oj.JobStatusID = js.JobStatusID
     where o.OrderID in (50211, 44621) and 
       (p.ProductID in (35,36,37,38,38,40,41,42,43) or  p.ProductID in (33,34))
  ) d
  unpivot
  (
    val
    for col in (Name, JobStatus)
  ) un
) s
pivot
(
  max(val)
  for col in (Name, JobStatusID,  JobStatusID1, Name1)
) piv;

Edit, if you are going to have an unknown number of values, then you will need to use dynamic SQL:

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

select @cols = STUFF((SELECT  ',' + QUOTENAME(col+cast(seq as varchar(10))) 
                    from
                    (
                       select   row_number() over(partition by o.OrderID,js.JobStatusID 
                          order by o.OrderID) seq
                       FROM NEPCCO.Orders o
                       inner join NEPCCO.Clients c on o.ClientID = c.ClientID
                       inner join NEPCCO.OrderJobs oj on o.OrderID = oj.OrderID
                       inner join NEPCCO.Suppliers s on oj.SupplierID = s.SupplierID
                       inner join NEPCCO.Products p on oj.ProductID = p.ProductID
                       inner join NEPCCO.OrderStatus os on o.OrderStatusID = os.OrderStatusID
                       inner join NEPCCO.JobStatus js on oj.JobStatusID = js.JobStatusID
                    ) d
                    cross apply
                    (
                      select 'Name', 1 union all
                      select 'JobStatusId', 2
                    ) c (col, so)
                    group by seq, col, so
                    order by seq, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT *
            from 
            (
               select OrderID, AccessName, Address1, Postcode, 
                GeneralInstructions, --CompanyName,
                col+cast(rn as varchar(10)) col,
                val
              from 
              (
                select o.OrderID, 
                  cast(js.JobStatusID as varchar(50)) JobStatusId, 
                  cast(p.Name as varchar(50)) Name, 
                  o.AccessName, o.Address1, o.Postcode, 
                  oj.GeneralInstructions, s.CompanyName
                 , row_number() over(partition by o.OrderID,js.JobStatusID 
                                  order by o.OrderID) rn
                 FROM NEPCCO.Orders o
                 inner join NEPCCO.Clients c on o.ClientID = c.ClientID
                 inner join NEPCCO.OrderJobs oj on o.OrderID = oj.OrderID
                 inner join NEPCCO.Suppliers s on oj.SupplierID = s.SupplierID
                 inner join NEPCCO.Products p on oj.ProductID = p.ProductID
                 inner join NEPCCO.OrderStatus os on o.OrderStatusID = os.OrderStatusID
                 inner join NEPCCO.JobStatus js on oj.JobStatusID = js.JobStatusID
                 where o.OrderID in (50211, 44621) and 
                   (p.ProductID in (35,36,37,38,38,40,41,42,43) or  p.ProductID in (33,34))
              ) d
              unpivot
              (
                val
                for col in (Name, JobStatus)
              ) un
            ) x
            pivot 
            (
                max(val)
                for col in (' + @cols + ')
            ) p '

execute(@query)

Upvotes: 3

Related Questions