escapeclaws escapeclaws - 2 days ago 7
SQL Question

SQL - Query to seperate single column and create new columns based on column data

Sorry, if the title is horrible. Have a table T. With a column Name, Num, and Status that either contains a I, O, or S.

Ex:

Name Num Status
Bob 1 I
Bob 2 O
Bob 3 O
John 4 I
John 5 S
Joe 6 O


Want result to look like:

Name Num I O S
Bob 1 x
Bob 2 x
Bob 3 X
John 4 X
John 5 X


Thanks!

Edit: Follow up question.

Name Num I O S
Bob 1 x
Bob 1 x
Bob 2 X


Need result:

Name Num I O S
Bob 1 X X
Bob 2 X


EDIT 2: Actual Query:

SELECT Name, Card_Nmbr,

[Out] = case when d.Status='I' then 'X' else '' end,
[In] = case when d.Status='O' then 'X' else '' end,
[Sales] = case when d.Status='S' then 'X' else '' end
FROM [PCOdb].[dbo].[GC_Header] as h
INNER JOIN GC_Detail as d on h.GC_TransNmbr = d.GC_TransNmbr
INNER JOIN GC_Master as m on d.GCM_Nmbr = m.GCM_Nmbr
INNER JOIN Galaxy1.dbo.GxUsers as u on h.UserID = u.UserID
WHERE GC_TransDate between '11/29/16' and dateadd(day,1, '11/29/16')
Group BY Card_Nmbr, Name

Answer

A simple case statement would help here

Select Name
      ,Num
      ,[I]  = case when [Status]='I' then 'X' else '' end
      ,[O]  = case when [Status]='O' then 'X' else '' end
      ,[S]  = case when [Status]='S' then 'X' else '' end
 From  YourTable

EDIT to handle multiple rows

Select Name
      ,Num
      ,[I]  = max(case when [Status]='I' then 'X' else '' end)
      ,[O]  = max(case when [Status]='O' then 'X' else '' end)
      ,[S]  = max(case when [Status]='S' then 'X' else '' end)
 From  YourTable
 Group By Name,Num

Edit 2 - Full Query (Assuming the Joins are doing what you need)

SELECT Name
      ,Card_Nmbr
      ,[Out]   = max(case when d.[Status]='I' then 'X' else '' end)
      ,[In]    = max(case when d.[Status]='O' then 'X' else '' end)
      ,[Sales] = max(case when d.[Status]='S' then 'X' else '' end)
 FROM  [PCOdb].[dbo].[GC_Header] as h
 JOIN  GC_Detail as d on h.GC_TransNmbr = d.GC_TransNmbr
 JOIN  GC_Master as m on d.GCM_Nmbr = m.GCM_Nmbr
 JOIN  Galaxy1.dbo.GxUsers as u on h.UserID = u.UserID
 WHERE GC_TransDate between '2016-11-29' and DateAdd(DD,1,'2016-11-29')
 Group BY Card_Nmbr, Name
Comments