How to write Unpivot query within Main Query?

I have a main query and in there there are columns that are bringing in data. I need to convert those columns into single column but be able to keep other elements in the query as is. Its writing UNPIVOT data query with the main to convert these individual columns.

From below query, STRING_ATTR1 through STRING_ATTR20 (all 20) needs to be UNPIVOT and presented as 2 columns one that contains “String Names” (i.e. STRING_ATTR1, STRING_ATTR2, etc) and 2nd column “String Data” that contains the actual unpivoted data.

Please help!

SELECT
[AGG_ELEMENT].[I_COUNT] AS [ELEMENT I COUNT],
[AGG_ELEMENT].[E_TIME_SUM] AS [ELEMENT E TIME],
[AGG_ELEMENT].[A_TIME_SUM] AS [ELEMENT A TIME],
[AGG_ELEMENT].[I_TIME_SUM] AS [ELEMENT I TIME],
[AGG_ELEMENT].[O_COUNT] AS [OPS COUNT],
[AGG_ELEMENT].[R_TIME_SUM] AS [R TIME],
[AGG_ELEMENT].[M_COUNT] AS [MSG COUNT],
[AGG_ELEMENT].[SE_COUNT] AS [SE COUNT],
[AGG_ELEMENT].[UE_COUNT] AS [UE COUNT],
[USERS].[ID] AS [USER ID],
[USERS].[LOGIN] AS [USER LOGIN],
[USERS].[DOMAIN] AS [USER DOMAIN],
[USERS].[NAME] AS [USER NAME],
[USERS].[STRING_ATTR1] AS [STRING_ATTR1],
[USERS].[STRING_ATTR2] AS [STRING_ATTR2],
[USERS].[STRING_ATTR3] AS [STRING_ATTR3],
[USERS].[STRING_ATTR4] AS [STRING_ATTR4],
[USERS].[STRING_ATTR5] AS [STRING_ATTR5],
[USERS].[STRING_ATTR6] AS [STRING_ATTR6],
[USERS].[STRING_ATTR7] AS [STRING_ATTR7],
[USERS].[STRING_ATTR8] AS [STRING_ATTR8],
[USERS].[STRING_ATTR9] AS [STRING_ATTR9],
[USERS].[STRING_ATTR10] AS [STRING_ATTR10],
[USERS].[STRING_ATTR11] AS [STRING_ATTR11],
[USERS].[STRING_ATTR12] AS [STRING_ATTR12],
[USERS].[STRING_ATTR13] AS [STRING_ATTR13],
[USERS].[STRING_ATTR14] AS [STRING_ATTR14],
[USERS].[STRING_ATTR15] AS [STRING_ATTR15],
[USERS].[STRING_ATTR16] AS [STRING_ATTR16],
[USERS].[STRING_ATTR17] AS [STRING_ATTR17],
[USERS].[STRING_ATTR18] AS [STRING_ATTR18],
[USERS].[STRING_ATTR19] AS [STRING_ATTR19],
[USERS].[STRING_ATTR20] AS [STRING_ATTR20],
[DAY].[TIMESTAMP] AS [DATE DAY],
[COMPUTER].[DOMAIN_NAME] AS [DOMAIN NAME],
[COMPUTER].[COMPUTER_NAME] AS [COMPUTER NAME],
[APPLICATION].[NAME] AS [APPLICATION NAME],
[MODULE].[NAME] AS [MODULE NAME],
[SCREEN].[NAME] AS [SCREEN NAME],
[SCREEN_TYPE].[NAME] AS [SCREEN TYPE]
FROM [AGG_ELEMENT] [AGG_ELEMENT]
LEFT JOIN [USERS] [USERS] ON ([AGG_ELEMENT].[USER_ID] = [USERS].[ID])
LEFT JOIN [DAY] [DAY] ON ([AGG_ELEMENT].[DAY_ID] = [DAY].[ID])
LEFT JOIN [COMPUTER] [COMPUTER] ON ([AGG_ELEMENT].[COMPUTER_ID] = [COMPUTER].[ID])
LEFT JOIN [APPLICATION] [APPLICATION] ON ([AGG_ELEMENT].[APPLICATION_ID] = [APPLICATION].[ID])
LEFT JOIN [MODULE] [MODULE] ON ([AGG_ELEMENT].[MODULE_ID] = [MODULE].[ID])
LEFT JOIN [SCREEN] [SCREEN] ON ([AGG_ELEMENT].[SCREEN_ID] = [SCREEN].[ID])
LEFT JOIN [SCREEN_TYPE] [SCREEN_TYPE] ON ([AGG_ELEMENT].[SCREEN_TYPE] = [SCREEN_TYPE].[ID])
WHERE
[DAY].[TIMESTAMP] BETWEEN '10/1/2019' AND '12/31/2019'

Answer

Just use OUTER APPLY:

SELECT v.*, . . .
FROM . . . OUTER APPLY
     (VALUES ('ATTR1', USERS.STRING_ATTR1),
             ('ATTR2', USERS.STRING_ATTR2),
             ('ATTR3', USERS.STRING_ATTR3),
              . . .
     ) v(which, val)
WHERE [DAY].[TIMESTAMP] BETWEEN '2019-10-01' AND '2019-12-31'

Advertisement

Answer