CODEWITHSUNDEEP
sqlsql-server
escapeclaws
escapeclaws

Reputation: 65

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

Upvotes: 1

Views: 591

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 82010

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

Upvotes: 2

M.Ali
M.Ali

Reputation: 69574

For SQL Server 2012 and Later you can use IIF (Transact-SQL) function too. 

Select Name 
      ,Num
      ,IIF([Status] = 'I' , 'X', '' ) AS [I]
      ,IIF([Status] = 'O' , 'X', '' ) AS [O]
      ,IIF([Status] = 'S' , 'X', '' ) AS [S]
FROM TableName

Upvotes: 0

ahmed abdelqader
ahmed abdelqader

Reputation: 3568

Use Case expression.

Demo:

Create table #temp
        (Name  varchar (10), 
        Num int , 
        [Status] char(1))

insert into #temp values ('Bob' ,  '1'  ,   'I')
insert into #temp values ('Bob'  , '2'  ,   'O')
insert into #temp values ('Bob'  , '3'  , 'O')
insert into #temp values ('John' , '4'  , 'I')
insert into #temp values ('John' , '5'  ,'S')
insert into #temp values ('Joe' ,  '6'  , 'O')



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 #temp

drop table #temp

Result:

enter image description here

Upvotes: 1

Related Questions