CODEWITHSUNDEEP
sqlsql-server
Nighthee
Nighthee

Reputation: 1387

Different columns of the same name SQL Sorted by Column Names

I have a table in SQL

Category | Subcategory | Region | Count | Percent |
    A    |      Aa     |   ON   |   3   |   5%    |
    A    |      Aa     |   AT   |   1   |   2%    |
    A    |      Ab     |   ON   |   5   |   10%   |
    B    |      Bc     |   AT   |   1   |   21%   |

But I want to be able to make an SQL statement that'll give me the query like this

Category | Subcategory | Region | Count | Percent | Region | Count | Percent |
    A    |      Aa     |   AT   |   1   |   2%    |   ON   |   3   |   5%    |
    A    |      Ab     | NULL/0 | NULL/0|  NULL/0 |   ON   |   5   |  10%    |
    B    |      Bc     |   AT   |   1   |   21%   | NULL/0 | NULL/0|  NULL/0 |

Would this be possible to do in SQL as a either a select or a stored procedure?

Upvotes: 1

Views: 91

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

As pointed out in the comments, you should use unique column names. To achieve your desired output, you can use conditional aggregation:

SQL Fiddle

SELECT
    Category,
    SubCategory,
    Region_AT = MAX(CASE WHEN Region = 'AT' THEN Region END),
    Count_AT = MAX(CASE WHEN Region = 'AT' THEN [Count] END),
    Percent_AT = MAX(CASE WHEN Region = 'AT' THEN [Percent] END),
    Region_ON = MAX(CASE WHEN Region = 'ON' THEN Region END),
    Count_ON = MAX(CASE WHEN Region = 'ON' THEN [Count] END),
    Percent_ON = MAX(CASE WHEN Region = 'ON' THEN [Percent] END)
FROM Tbl
GROUP BY Category, SubCategory

If you do not know how many Regions are there, you can use dynamic crosstab:

SQL Fiddle

DECLARE @sql1 NVARCHAR(2000) = '',
        @sql2 NVARCHAR(2000) = '',
        @sql3 NVARCHAR(2000) = ''

SELECT @sql1 =
'SELECT
    Category
    , SubCategory' + CHAR(10)

SELECT @sql2 = @sql2 +
'   , MAX(CASE WHEN Region = ''' + Region + ''' THEN Region END) AS Region_' + Region + CHAR(10) +
'   , MAX(CASE WHEN Region = ''' + Region + ''' THEN [Count] END) AS Count_' + Region + CHAR(10) +
'   , MAX(CASE WHEN Region = ''' + Region + ''' THEN [Percent] END) AS Percent_' + Region + CHAR(10)
FROM(
    SELECT DISTINCT Region FROM Tbl
)t

SELECT @sql3 = 
'FROM Tbl
GROUP BY Category, SubCategory'

PRINT (@sql1 + @sql2 + @sql3)
EXEC (@sql1 + @sql2 + @sql3)

You can read more about dynamic crosstab in this great article by Jeff Moden.

Upvotes: 1

Related Questions