将值拆分为sql server中的case语句的列

Question

需要帮助分割列中的值。这里是我到目前为止

select 
recordid, productname, 
case when future06 like '%418%' then (Books 
case when future06 like '%421%' then (Video) else null 
end 
from schema.dbo.table1 

Books和Video正在future06列两个主要产品。 而不是有future06作为第三列，我想有 Books和Video旁边recordid和productname。

将生活有输出的样子：

RecordID ProductName Books Video 

Answer 1

你几乎拥有了：

select 
    RecordID 
    , ProductName 
    , Books = case when future06 like '%418%' then future06 else null end 
    , Video = case when future06 like '%421%' then future06 else null end 
from 
    schema.dbo.table1 

Answer 2

select 
recordid, productname, 
case when future06 like '%418%' then future06 else null end as Books, 
case when future06 like '%421%' then future06 else null end as Video 
from schema.dbo.table1 


select 
recordid, productname, 
COUNT(case when future06 like '%418%' then 1 else 0 end) as NoOfBooks, 
COUNT(case when future06 like '%421%' then 1 else 0 end) as NoOfVideo 
from schema.dbo.table1 
group by recordid, productname 

select 
recordid, productname, 
COUNT(case when future06 like '%418%' then 1 else 0 end) as NoOfBooks, 
COUNT(case when future06 like '%421%' then 1 else 0 end) as NoOfVideo, 
COUNT(*) as Total 
from schema.dbo.table1 
group by recordid, productname