Search code examples
sqlsql-servergroup-bysubqueryinner-join

group by category and add extra columns

Sorry if the the title is not descriptive enough. So I decided to upload the photos.

I would like to know the most efficient way to turn the table 1 into table 2.Currently I inner join a few sub-queries and I feel like that is not the best way.

table 1

table 2

Solution

  • First, data definition (since you didn't provide any insert query, I written script for generating it):

    declare @table table(issueDsc varchar(20), cfName varchar(20), cfValue varchar(20))
insert into @table values
('cat 11.1 text1','Location','Sydney'),
('cat 11.1 text1','MC Response Date',null),
('cat 11.1 text1','VPR','no'),
('cat 11.1 text2','Location','Melbourne'),
('cat 11.1 text2','MC Response Date',null),
('cat 11.1 text2','VPR',null),
('cat 11.1 text3','Location',null),
('cat 11.1 text3','MC Response Date','2018-03-24'),
('cat 11.1 text3','VPR','yes')

    Here's query, which will result in your desired recordset:

    --catDsc is fetched from between cat and text word (achieved with charindex functions)
select substring(issueDsc, charindex('cat', issueDsc) + 3, charindex('text', issueDsc) -charindex('cat', issueDsc)- 4) [catDsc],
       [issueDsc],
       [Location],
       [VPR],
       [MC Response Date] 
from @table
--syntax for this part is very simple and you could goole it in order to better understand
pivot
(
    MAX(cfValue)
    for cfName in ([Location],[VPR],[MC Response Date])
) as pivoted