Convert some Rows into Columns

Question

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

possible duplicate of [Convert Database Rows into Columns](http://stackoverflow.com/questions/1946234/convert-database-rows-into-columns) — GolezTrol, Feb 20 '15 at 20:13
Look at "crosstab" and/or "pivot" - there are lots of documented approaches out there. — snow_FFFFFF, Feb 20 '15 at 20:13
@GolezTro Sorry, it MSSQL. I've looked into pivot tables but every guide says i need to have an aggregate function. I'm not looking to sum, count, avg.. ect. I'm not sure how to get around that. — Kevin, Feb 20 '15 at 20:34
@Kevin, MAX is an aggregate function. It is what you need here to make this work. — Sean Lange, Feb 20 '15 at 20:55
Here is what doesn't make sense. How do you know which Q-0005 goes with which other rows? There is two values for PAL, 1 CompID and 1 CompLo. Yet in your desired output there are 3 rows. What is logic or business rule you are trying to achieve here? — Sean Lange, Feb 20 '15 at 21:02

score 1 · Accepted Answer · edited Feb 20 '15 at 21:34

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.

Thanks! I was going crazy with trying to get a pivot table, much cleaner this way. — Kevin, Feb 20 '15 at 21:33
@Kevin, no problem! Yeah, pivot can be a pain when you need to pivot multiple columns. — animateme, Feb 20 '15 at 21:36

Convert some Rows into Columns

1 Answers1