Get a substring from a column and perform a groupB

Question

I have a table that stores data about a large number of files, such as their language, unique ID, file path etc. I want to be able to get the sub-string from the unique ID which gives me the asset type, this is always the first 2 letters of the ID. I then want to group these asset types by language and have a count for how many of each type every language has. So at the end I would ideally like a table that has a language column and then a column for each substring (asset type).

I have tried to create a large switch statement but this isn't very reliable and I was told maybe linq would be better. I don't have much experience with linq or sql and I have a couple of sql queries I've tried that gets me one part of the desired results, but I was hoping maybe someone who has more experience might know how to group these functions into one statement.

SELECT 
  LCID,
  SUBSTRING(AssetID,1,2)  
FROM [table]

this gets me the correct substrings, but I have multiple rows for each language. Is there any way to group the same languages into one column and then count how many of each type there are? Thanks

Answer 1

Sounds like you want a COUNT and a GROUP BY:

SELECT 
  SUBSTRING(AssetID,1,2), 
  COUNT(*) Total
FROM [table]
GROUP BY SUBSTRING(AssetID,1,2)

You did not specify what database but, if you are using SQL Server and LCID is in your SELECT statement, then you will need to include it in your GROUP BY clause.

If the LCID value is unique for each row then you will get multiple records for each AssetID because it will try to group the unique values together. As a result, I removed the LCID.

If it is not unique, then you can use:

SELECT LCID, 
  SUBSTRING(AssetID,1,2), 
  COUNT(*) Total
FROM [table]
GROUP BY LCID, SUBSTRING(AssetID,1,2)

Based on the edits that you made, you want a PIVOT which transforms the data from rows into columns. For a PIVOT you will use:

select LCID, HA, HT, HP, FH, FX
from
(
  SELECT LCID, 
    SUBSTRING(AssetID,1,2) AssetID
  FROM [table]
) src
pivot
(
  count(AssetID)
  for AssetID in (HA, HT, HP, FH, FX) -- place more values here
) piv

If the values are unknown that you want to transform into columns, then you will need to use dynamic SQL similar to this:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(SUBSTRING(AssetID,1,2)) 
                    from [table]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT LCID, ' + @cols + ' from 
             (
                SELECT LCID, 
                  SUBSTRING(AssetID,1,2) AssetID
                FROM [table]
            ) x
            pivot 
            (
                count(AssetID)
                for AssetID in (' + @cols + ')
            ) p '

execute(@query)