在SQL中将列值重复为行

I'm wondering how to turn a column which contains repetitive column values into rows in MSSQL. Here is an example of what I'm working with.

我想知道如何将包含重复列值的列转换为MSSQL中的行。这是我正在使用的一个例子。

CREATE TABLE Colors
(
    Time_ Time,
    Color varchar(6),
    count int
)

INSERT INTO Colors VALUES ('10:00', 'Red', 2);
INSERT INTO Colors VALUES ('10:00', 'Orange', 3);
INSERT INTO Colors VALUES ('10:00', 'Yellow', 6);
INSERT INTO Colors VALUES ('10:00', 'Green', 9);
INSERT INTO Colors VALUES ('10:00', 'Blue', 2);
INSERT INTO Colors VALUES ('11:00', 'Red', 3);
INSERT INTO Colors VALUES ('11:00', 'Orange', 4);
INSERT INTO Colors VALUES ('11:00', 'Yellow', 6);
INSERT INTO Colors VALUES ('11:00', 'Green', 7);
INSERT INTO Colors VALUES ('11:00', 'Blue', 1);
INSERT INTO Colors VALUES ('12:00', 'Red', 2);
INSERT INTO Colors VALUES ('12:00', 'Orange', 2);
INSERT INTO Colors VALUES ('12:00', 'Yellow', 7);
INSERT INTO Colors VALUES ('12:00', 'Green', 9);
INSERT INTO Colors VALUES ('12:00', 'Blue', 4);

And SQLFiddle: http://sqlfiddle.com/#!3/48657/1

和SQLFiddle:http://sqlfiddle.com/#!3/48657/1

What I'm looking for is a table where the rows are Time_, Red, Orange, Yellow, Green, Blue. So it would look something like this:

我正在寻找的是一个表格,其中的行是Time_,Red,Orange,Yellow,Green,Blue。所以它看起来像这样:

Any help would be appreciated.

任何帮助,将不胜感激。

1 个解决方案

#1

SELECT  Time_,
        Red, 
        Orange, 
        Yellow, 
        Green, 
        Blue
FROM
    (
        SELECT  Time_, Color, Count
        FROM    Colors
    ) orgData
    PIVOT
    (
        MAX(Count)
        FOR Color IN (Red, Orange, Yellow, Green, Blue)
    ) pvt

• SQLFiddle Demo