CODEWITHSUNDEEP
sqlsql-server
James Khan
James Khan

Reputation: 841

Pivot query assistance

Job Batch id   Company Outlet Id   Clearance required   Hanky required 
1              10                  T                    T

Now I want the the following 

Job Batch id   Company Outlet ID    Reason    
1              10                   Clearance Required , Hanky Required

My brain has frozen so was needing assistance ?

How to construct this unpivot query ?

Upvotes: 0

Views: 45

Answers (4)

user359040
user359040

Reputation:

Try:

select [Job Batch id], [Company Outlet Id],
       case [Clearance required]
            when 'T' then 'Clearance Required' + 
                case [Hanky required] when 'T' then ' , ' else '' end
       else ''
       end + case [Hanky required] when 'T' then 'Hanky Required' else '' end as [Reason]
from theTable

Upvotes: 1

sgeddes
sgeddes

Reputation: 62841

Here you go -- just combine your columns like such. I'm using STUFF to remove the beginning comma:

select JobBatchId, 
  CompanyOutletId,
  STUFF(
    ISNULL(CASE WHEN ClearanceRequired = 'T' THEN ',Clearance Required' END, '')  +
    ISNULL(CASE WHEN HankyRequired = 'T' THEN ',Hanky Required' END, '') 
    , 1, 1, '') Reasons
from YourTable

And the SQL Fiddle.

Upvotes: 1

Taryn
Taryn

Reputation: 247700

You can use UNPIVOT, CROSS APPLY and FOR XML PATH to get the result:

;with cte as
(
  select [Job Batch id], [Company Outlet Id],
    col, value
  from yourtable
  unpivot
  (
    value
    for col in ([Clearance required], [Hanky required])
  ) unpiv
)
select distinct t1.[Job Batch id], 
  t1.[Company Outlet Id],
  left(s.reason, len(s.reason)-1) reason
from cte t1
cross apply 
(
  select t2.col + ', '
  FROM cte t2
  where t1.[Job Batch id] = t2.[Job Batch id]
    and t1.[Company Outlet Id] = t2.[Company Outlet Id]
  FOR XML PATH('')
) s (reason)

See SQL Fiddle with Demo

Or you can use UNPIVOT, STUFF and FOR XML PATH:

;with cte as
(
  select [Job Batch id], [Company Outlet Id],
    col, value
  from yourtable
  unpivot
  (
    value
    for col in ([Clearance required], [Hanky required])
  ) unpiv
)
select distinct t1.[Job Batch id], 
  t1.[Company Outlet Id],
  STUFF(
         (SELECT ', ' + col
          FROM cte t2
          where t1.[Job Batch id] = t2.[Job Batch id]
            and t1.[Company Outlet Id] = t2.[Company Outlet Id]
          FOR XML PATH (''))
          , 1, 1, '')  AS Reason
from cte t1

See SQL Fiddle with Demo

Upvotes: 1

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

I'd advise against putting multiple values within a single column (Reason) in your Sql query, and instead leave that up to your presentation layer if that's how you want to see the data...

But here's how you can do it in Sql:

SELECT
    [Job Batch Id],
    [Company Outlet Id],
    CASE 
        WHEN [Clearance Required] = 'T' 
        THEN 'Clearance Required' 
        ELSE '' END +
    -- Determine if the comma is needed or not...
    CASE 
        WHEN [Clearance Required] = 'T' 
            AND [Hanky Required] = 'T' 
        THEN ' , ' 
        ELSE '' END +
    CASE
        WHEN [Hanky Required] = 'T' 
        THEN 'Hanky Required' 
        ELSE '' END AS Reason
FROM YourTable

Upvotes: 1

Related Questions