Better way to flatten meta type data?

  • I have the following table how would I be able to get a view similar to

    SELECT [ID], [NAME], [Imaging], [Long Term], [Auto], [Factory], [Central Office], [Pizza] FROM [Some SQL Magic Code];

    The best I've come up with is multiple selects with where clauses to create tables and then join all of them.  I'm not even sure what to call this, as UNPIVOT seems the closest, but that changes columns into rows and I want multiple rows into one row (Metadata transformation?).  Of course there is a [Type] can be anything so the end result will have an unknown number of columns.

    CREATE TABLE [dbo].[SystemComponents]
    [SystemComponentsId] INT IDENTITY(1, 1)
    , [ID] INT NULL
    , [NAME] VARCHAR(255) NULL
    , [TYPE] VARCHAR(255) NULL

    INSERT INTO [dbo].[SystemComponents] ([ID], [NAME], [TYPE], [COUNT])
    SELECT 1042, 'Southern', 'Imaging', 12 UNION ALL
    SELECT 1042, 'Southern', 'Long Term', 7 UNION ALL
    SELECT 1042, 'Southern', 'Auto', 8 UNION ALL
    SELECT 1042, 'Southern', 'Factory', 1 UNION ALL
    SELECT 1044, 'Northern', 'Imaging', 20 UNION ALL
    SELECT 1044, 'Northern', 'Long Term', 3 UNION ALL
    SELECT 1044, 'Northern', 'Auto', 2 UNION ALL
    SELECT 1044, 'Northern', 'Factory', 1 UNION ALL
    SELECT 1044, 'Northern', 'Central Office', 1 UNION ALL
    SELECT 1045, 'Eastern', 'Imaging', 3 UNION ALL
    SELECT 1045, 'Eastern', 'Auto', 2 UNION ALL
    SELECT 1045, 'Eastern', 'Factory', 7 UNION ALL
    SELECT 1045, 'Eastern', 'Pizza', 42 UNION ALL
    SELECT 1046, 'NE', 'Imaging', 4 UNION ALL
    SELECT 1046, 'NE', 'Auto', 6 UNION ALL
    SELECT 1046, 'NE', 'Factory', 1 UNION ALL
    SELECT 1047, 'SE', 'Imaging', 5 UNION ALL
    SELECT 1047, 'SE', 'Auto', 3;
  • Like this?

    SELECT ct.[NAME]
    , SUM(ct.c_Imaging) AS Imaging
    , SUM(ct.c_Long_Term) AS Long_Term
    , SUM(ct.c_Factory) AS Factory
    , SUM(ct.c_Central_Office) AS Central_Office
    , SUM(ct.c_Pizza) AS Pizza
    , [Name]
    , CASE WHEN [Type] = 'Imaging' THEN [Count] ELSE 0 END c_Imaging
    , CASE WHEN [Type] = 'Long Term' THEN [Count] ELSE 0 END c_Long_Term
    , CASE WHEN [Type] = 'Auto' THEN [Count] ELSE 0 END c_Auto
    , CASE WHEN [Type] = 'Factory' THEN [Count] ELSE 0 END c_Factory
    , CASE WHEN [Type] = 'Central Office' THEN [Count] ELSE 0 END c_Central_Office
    , CASE WHEN [Type] = 'Pizza' THEN [Count] ELSE 0 END c_Pizza
    FROM SystemComponents) ct
    GROUP BY ct.[NAME]
  • There is no need for the sub-query

    SELECT    sc.ID
    , sc.[Name]
    , Imaging = SUM( CASE WHEN sc.[Type] = 'Imaging' THEN sc.[Count] ELSE 0 END )
    , Long_Term = SUM( CASE WHEN sc.[Type] = 'Long Term' THEN sc.[Count] ELSE 0 END )
    , [Auto] = SUM( CASE WHEN sc.[Type] = 'Auto' THEN sc.[Count] ELSE 0 END )
    , Factory = SUM( CASE WHEN sc.[Type] = 'Factory' THEN sc.[Count] ELSE 0 END )
    , Central_Office = SUM( CASE WHEN sc.[Type] = 'Central Office' THEN sc.[Count] ELSE 0 END )
    , Pizza = SUM( CASE WHEN sc.[Type] = 'Pizza' THEN sc.[Count] ELSE 0 END )
    FROM dbo.SystemComponents AS sc
    GROUP BY sc.ID, sc.[Name];
  • If you have an unknown list of [Type], dynamic cross-tab queries are dealt with expertly and in great detail in these two Jeff Moden articles:


  • Thanks!  The SUM with a CASE statement solve my problem for now and the links to the dynamic process will help long term once I can consume and understand them!

    • This reply was modified 1 day, 20 hours ago by  jeff.born.
  • 1) You need another table with just ID and NAME.  Repeating the same name in multiple rows is a 1NF violation.

    2) The clustering key to this table should be (ID, SystemComponentsId) and NOT just (SystemComponentsId).

    3) The TYPE should really be encoded, that is 1 = 'Imaging', 2 = 'Long Term', etc., with a separate table for the TYPE_CODE and type (type_code maybe just tinyint or at most smallint, unless you could have > ~65000 types, which seems extremely unlikey).  The separate table means that when a TYPE description changes, you only have to change it in one place, and not across many rows.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply