Convert Multiple Nested SELECT Statements into a PIVOT Table

Question

I wish to display my records horizontally (Rows to Columns) and have created the following SQL statement that does the job but I think it could be bettered.

I have researched PIVOT Tables and believe they would make it much cleaner/efficeint, I just cannot get the PIVOT to work correctly. All suggestions welcomed:

SELECT  (SELECT
                COUNT(*)
            FROM ImportItem
            WHERE BodyTypeID = 1)
        AS [Sedan]
        ,(SELECT
                COUNT(*)
            FROM ImportItem
            WHERE BodyTypeID = 2)
        AS [Sport utility]
        ,(SELECT
                COUNT(*)
            FROM ImportItem
            WHERE BodyTypeID = 3)
        AS [Sport coupe]
        ,(SELECT
                COUNT(*)
            FROM ImportItem
            WHERE BodyTypeID = 4)
        AS [Station wagon]
        ,(SELECT
                COUNT(*)
            FROM ImportItem
            WHERE BodyTypeID = 5)
        AS [Convertible]
        ,(SELECT
                COUNT(*)
            FROM ImportItem
            WHERE BodyTypeID = 6)

        AS [Coupe]
        ,(SELECT
                COUNT(*)
            FROM ImportItem
            WHERE BodyTypeID = 7)

        AS [Roadster]
        ,(SELECT
                COUNT(*)
            FROM ImportItem
            WHERE BodyTypeID = 8)

        AS [Mini van]
        ,(SELECT
                COUNT(*)
            FROM ImportItem
            WHERE BodyTypeID = 9)
        AS [Pick-up]
        ,(SELECT
                COUNT(*)
            FROM ImportItem
            WHERE BodyTypeID = 10)
        AS [Hatchback]
        ,(SELECT
                COUNT(*)
            FROM ImportItem
            WHERE BodyTypeID = 11)
        AS [Cargo van]

Thanks

Post your attempt at using PIVOT and any errors you got, so that we can help debug it. — Tab Alleman, Jul 07 '16 at 18:09
I ended up deleting it as I couldn't get it working. I am pretty much a beginner when it comes to SQL — James, Jul 07 '16 at 18:27
Can you post your sample data and expected results then? i.e. what the data looks like now and what you want it to look like? — S3S, Jul 07 '16 at 18:37
In that case, your question is a duplicate of the many other questions asking how to convert rows to columns. Try this one: http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server — Tab Alleman, Jul 07 '16 at 18:43
I was asking if anyone could assist me with converting this. I have tried the various posts regarding pivoting but I cannot figure it out. Perhaps someone will help me and then future people who cannot figure out will use my question as an example. Just a thought.... — James, Jul 07 '16 at 18:45

score 1 · Accepted Answer · answered Jul 07 '16 at 19:01

1

SELECT  COUNT(CASE WHEN BodyTypeID = 1 THEN 1 END) AS [Sedan],
        COUNT(CASE WHEN BodyTypeID = 2 THEN 1 END) AS [Sport utility],
        COUNT(CASE WHEN BodyTypeID = 3 THEN 1 END) AS [Sport coupe],
        etc..
FROM    ImportItem

or

SELECT  [1] AS [Sedan],
        [2] AS [Sport utility],
        [3] AS [Sport coupe],
        ect..
FROM    (SELECT BodyTypeID FROM ImportItem) t
PIVOT   (COUNT(BodyTypeID) FOR BodyTypeID IN ([1],[2],[3],etc..)) p

answered Jul 07 '16 at 19:01

JamieD77

13,796
1
17
27

I did not see your second solution when I posted mine, which mirrored yours. I have deleted mine and given you an up tick. I don't want to steal your thunder :). – TLaV Jul 07 '16 at 19:57
@TLaV lol i was just being facetious. i was actually waiting for this question to get closed since it's been duplicated so many times – JamieD77 Jul 07 '16 at 20:22

Convert Multiple Nested SELECT Statements into a PIVOT Table

1 Answers1