CODEWITHSUNDEEP
sqlsql-server
Kevin
Kevin

Reputation: 23

Convert some Rows into Columns

I'm not sure exactly how I want to ask this question so I'll provide the data and the end results I'm looking for. 

Q Number | M Number | Date   | Type    | Result
------------------------------------------------
Q-0005   | M-00099  | 2/2/15 | Pal     | 1
Q-0005   | M-00099  | 2/2/15 | Pal     | 2
Q-0007   | M-00095  | 2/2/15 | Pal     | 1
Q-0007   | M-00095  | 2/1/15 | Pal     | 3
Q-0005   | M-00099  | 2/1/15 | CompID  | 25AD
Q-0007   | M-00095  | 2/2/15 | CompID  | 15AD
Q-0005   | M-00099  | 2/1/15 | CompLO  | ZYZ23
Q-0007   | M-00095  | 2/2/15 | CompLO  | ZYZ23

This is how the data is stored within SQL Server.

However I would like to be able to sort by Type and they're result. So, if I wanted to find the Q-Number where Pal = 1 and CompID = 25AD and CompLO = ZYZ23 I can't seem to find a way to pivot the Type column, if that's even possible. 

Q Number | M Number | Date   | PAL  | CompID | CompLO
------------------------------------------------------
Q-0005   | M-00099  | 2/2/15 | 1    | 25AD   | ZYZ23
Q-0005   | M-00099  | 2/2/15 | 2    | 25AD   | ZYZ23
Q-0005   | M-00099  | 2/2/15 | 3    | 25AD   | ZYZ23
Q-0007   | M-00095  | 2/1/15 | 3    | 15AD   | ZYZ23

Any help would be appreciated.

-Kevin

Upvotes: 0

Views: 92

Answers (1)

animateme
animateme

Reputation: 309

Here is an example of how you can pivot your data... My output does not match what you specify in your example, but I'm thinking that you made a typo in the Date column causing some of the columns not to group correctly.

If there is not a typo in your example dataset, then please let me know how you are managing to calculate the output your provided and I will update my answer.

------------------------------------
-- Mock up the table and data...
------------------------------------
create table #Table
(
    QNumber CHAR(6),
    MNumber CHAR(7),
    Date    DATE,
    Type    VARCHAR(20),
    Result  VARCHAR(20)
);
go

insert into #Table(QNumber, MNumber, Date, Type, Result)
values      ('Q-0005', 'M-00099', '2/2/15', 'Pal', '1'),
            ('Q-0005', 'M-00099', '2/2/15', 'Pal', '2'),
            ('Q-0007', 'M-00095', '2/2/15', 'Pal', '1'),
            ('Q-0007', 'M-00095', '2/1/15', 'Pal', '3'),
            ('Q-0005', 'M-00099', '2/1/15', 'CompID', '25AD'),
            ('Q-0007', 'M-00095', '2/2/15', 'CompID', '15AD'),
            ('Q-0005', 'M-00099', '2/1/15', 'CompLO', 'ZYZ23'),
            ('Q-0007', 'M-00095', '2/2/15', 'CompLO', 'ZYZ23');
go
------------------------------------

select      QNumber,
            MNumber,
            Date,
            PAL     =   MAX(case
                                when Type = 'PAL' then Result
                            end),
            CompID  =   MAX(case
                            when Type = 'CompID' then Result
                        end),
            CompLO  =   MAX(case
                            when Type = 'CompLO' then Result
                        end)
from        #Table
group by    QNumber, MNumber, Date;

Here is a sample: sqlfiddle.

Upvotes: 1

Related Questions