CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Justin
Justin

Reputation: 972

Formatting Table Results in SQL

This is probably quite simple - but i'm new to SQL and not sure what functions to use.

I have a table that holds the following data:

Costcode        CostDescr
_________________________________
4000            Product 
4001            Other 
4100            Product 
4101            Other 
5000            Product 
5001            Other 
5100            Product 
5101            Other

I am trying to get the output to look like this:

X Sales         Y Sales          CostDescr       X Purchases       Y Purchases
_______________________________________________________________________________
 4000             4100          Product              5000             5100
 4001             4101          Other                5001             5101

How is it possible to create this format from the results?

Upvotes: 3

Views: 154

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Using this query:

SELECT CASE 
         WHEN Costcode >= 4000 AND Costcode < 4100 THEN 'X Sales'
         WHEN Costcode >= 4100 AND Costcode < 5000 THEN 'Y Sales'
         WHEN Costcode >= 5000 AND Costcode < 5100 THEN 'X Purchases'
         WHEN Costcode >= 5100 THEN 'Y Purchases'
       END As PivotColumn,
       SUBSTRING(CostDescr, 1, CHARINDEX(' ', CostDescr, 1) - 1) AS CostType,
       Costcode
FROM #Sales

you get a table-valued expression on to which pivot can be performed. This is the output of the above statement using the sample data you provide:

PivotColumn CostType    Costcode
---------------------------------
X Sales     Product     4000
X Sales     Other       4001
Y Sales     Product     4100
Y Sales     Other       4101
X Purchases Product     5000
X Purchases Other       5001
Y Purchases Product     5100
Y Purchases Other       5101

Thus, the following query gives the required output:

SELECT CostType, [X Sales] AS [X Sales], [Y Sales] AS [Y Sales], 
                 [X Purchases] AS [X Purchases], [Y Purchases] AS [Y Purchases]
FROM 
(SELECT CASE 
        WHEN Costcode >= 4000 AND Costcode < 4100 THEN 'X Sales'
        WHEN Costcode >= 4100 AND Costcode < 5000 THEN 'Y Sales'
        WHEN Costcode >= 5000 AND Costcode < 5100 THEN 'X Purchases'
        WHEN Costcode >= 5100 THEN 'Y Purchases'
       END As PivotColumn,
       SUBSTRING(CostDescr, 1, CHARINDEX(' ', CostDescr, 1) - 1) AS CostType,
       Costcode       
FROM #Sales) s
PIVOT
(
AVG (Costcode)
FOR PivotColumn IN ( [X Sales], [Y Sales], [X Purchases], [Y Purchases] )
) AS pvt

Output:

    CostType    X Sales Y Sales X Purchases Y Purchases
   ------------------------------------------------------
    Other       4001    4101    5001        5101
    Product     4000    4100    5000        5100

Upvotes: 3

Related Questions