CODEWITHSUNDEEP
sql-server
user2115472
user2115472

Reputation: 137

Rows to Column conversion Sql query SQL SERVER

I have a table name #Table1(See the attachment) I want following out put (See the attachment)

enter image description here

Upvotes: 1

Views: 72

Answers (2)

qxg
qxg

Reputation: 7036

@Raging Bull's answer is correct. Here is version using PIVOT

SELECT FormatType, [True], [False], [Blank], [True] + [False] + [Blank] AS Total
FROM
(
    SELECT FormatType, Result
    FROM Table1
) AS SourceTable
PIVOT
(
    COUNT(Result)
    FOR Result IN ([True], [False], [Blank])
) AS PivotTable

It produces the exact same result.

See result in SQL Fiddle

Upvotes: 4

Raging Bull
Raging Bull

Reputation: 18747

Try this:

SELECT FormatType,
       ISNULL(COUNT(CASE WHEN Result='True' THEN '1' END),0) AS [True],
       ISNULL(COUNT(CASE WHEN Result='False' THEN '1' END),0) AS [False],
       ISNULL(COUNT(CASE WHEN Result='Blank' THEN '1' END),0) AS [Blank],
       ISNULL(COUNT(1),0) AS [Total]
FROM Table1
GROUP BY FormatType
ORDER BY FormatType DESC

Explanation:

This query will select the FormatType along the count of each cases and the total. ISNULL is used for replacing NULL values with 0 (in case of FALSE in ASP).

Result:

FORMATTYPE  TRUE    FALSE   BLANK   TOTAL
PSP         1       2       1       4
ASP         1       0       2       3

See result in SQL Fiddle.

Upvotes: 4

Related Questions