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
, [COUNT] INT 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;
January 13, 2021 at 2:07 am
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
FROM
(SELECT ID
, [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]
January 13, 2021 at 6:55 am
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:
https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs
January 13, 2021 at 9:58 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply