Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Posts
    32

    Unhappy Unanswered: Help with (Pivot/Cross-Join???) query to select a result set

    I have information on clothes in a table that I want to select out to a result set in a different structure - I suspect that this will include some kind of pivot (or cross-join?) but as I've never done this before I'd appreciate any kind of help possible.

    Current structure is:

    Colour Size Quantity
    -----------------------
    Red 10 100
    Red 12 200
    Red 14 300
    Blue 10 400
    Blue 12 500
    Blue 14 600
    Green 10 700
    Green 12 800
    Green 14 900
    Green 16 1000

    I want to produce this result set:

    Colour Size10 Size12 Size14 Size16
    -------------------------------------
    Red 100 200 300 0
    Blue 400 500 600 0
    Green 700 800 900 1000

    There could be any number of sizes or colours.

    Is this possible? Can anyone give me any pointers?

    Thanks in advance

    greg

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Nobody here is going to give a better explanation than that which is found in Books Online, which is excellent. Search BOL for "Crosstab".

    I will tell you that it is almost always preferable to let your reporting interface handle pivoting your data, rather than having SQL Server do it. It is really more of a presentation issue.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Posts
    32
    Thanks - that was useful.

    I have the Query below which works. However, the Size is hardcoded into the CASE statements - is it possible to rewrite this so that I can get it to work for any number of sizes/size types, and create their corresponding columns automatically? I don't necessarily know all the possible sizes, and they may not be numerical....

    Greg

    ------------------------

    CREATE TABLE ClothTab( Colour VARCHAR(50), ClothSize INT, Quantity Int)
    GO
    INSERT INTO clothtab VALUES ('Red',10,100)
    INSERT INTO clothtab VALUES ('Red', 12, 200)
    INSERT INTO clothtab VALUES ('Red', 14, 300)
    INSERT INTO clothtab VALUES ('Blue', 10, 400)
    INSERT INTO clothtab VALUES ('Blue', 12, 500)
    INSERT INTO clothtab VALUES ('Blue', 14, 600)
    INSERT INTO clothtab VALUES ('Green', 10, 700)
    INSERT INTO clothtab VALUES ('Green', 12, 800)
    INSERT INTO clothtab VALUES ('Green', 14, 900)
    INSERT INTO clothtab VALUES ('Green', 16, 1000)
    GO

    SELECT Colour,
    SUM(CASE ClothSize WHEN 10 THEN Quantity ELSE 0 END) AS '10',
    SUM(CASE ClothSize WHEN 12 THEN Quantity ELSE 0 END) AS '12',
    SUM(CASE ClothSize WHEN 14 THEN Quantity ELSE 0 END) AS '14',
    SUM(CASE ClothSize WHEN 16 THEN Quantity ELSE 0 END) AS '16'
    FROM clothtab
    GROUP BY Colour
    ORDER By Colour DESC
    GO

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Not without heavy and cumbersome SQL. But the point I was trying to make earlier is that any decent reporting tool (Crystal, Access, etc...) can easily format your data as a crosstab. You should let your interface handle presenting the data, and just use SQL Server for generating the data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jan 2005
    Posts
    10
    I agree that presentation requirements would be better implemented on the reporting tool rather than in SQL.

    Just a comment on your query though. Although it is possible to cascade CASE in a query, it would have some drawbacks. One is processing speed. Rather than casade your query with so many CASE, you might as well implement it in different SELECTs then combine them using JOIN.

    The query might be longer but the better processing speed and simplicity of the query is worth it.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd like to see some benchmarks on the two different approaches, since the one gregclark implemented is straight out of Books Online.

    Seems to me the CASE method is making only a single pass through the dataset, while what you are suggesting would require a pass for each column.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2005
    Location
    Avon Lake, OH
    Posts
    112

    Lightbulb

    I agree that crosstabs are probably better left to Crystal, etc.

    That said, how about generating the SQL code dynamically. E.g. preselect the sizes existing in the database, using that pre-selection to create the cases:

    DECLARE @SQL NVARCHAR(4000)
    SET @SQL = N'SELECT Colour'
    SELECT @SQL = @SQL + N', SUM(CASE ClothSize WHEN ' +
    CAST(ClothSize AS NVARCHAR) +
    N' THEN Quantity ELSE 0 END) AS ''' + CAST(ClothSize AS NVARCHAR) + N'''' FROM
    (
    SELECT DISTINCT ClothSize FROM clothtab
    ) AS Sizes

    SET @SQL = @SQL + N' FROM clothtab GROUP BY Colour ORDER By Colour DESC'
    EXEC sp_executesql @SQL

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    As a matter of fact I'm comprising such a query right now, with the added challenge of allowing the user to customize the order of the columns. I just wouldn't recommend this for most SQL Developers.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Posts
    32
    Thanks for your replies guys - point taken about using crystal/Excel etc.. however my colleague (for whom the question was on behalf of) wants to do it with SQL.

    He has found some something useful here:
    http://www.itrain.de/knowhow/sql/tsq...sform_v1_1.asp

    Greg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •