How to query SQL dynamic lookup

Question

I have to pull data from these tables

**PRODUCT**                 **LOOKUP_CATEGORY**
ID      ProductName         ID      Category
1       Memory1             1       Product Type
2       Memory2             2       DIMM Type
3       Headset1            3       Color
4       Headset2            4       Speed
5       Keyboard1           5       Form Factor
6       Keyboard2

**LOOKUP**
ID      CategoryID       Value
1       1                DRAM
2       1                Keyboard
3       1                Headset
4       2                Buffered
5       2                Unbuffered
6       3                Red
7       3                White
8       3                Yellow
9       4                2000Mhz
10      4                2300Mhz
11      4                2600Mhz
12      5                DIMM
13      5                SODIMM
14      5                Earbud
15      5                On-Ear

**PRODUCT_FEATURES**
ID     ProductID     LookupID
1      1             1
2      1             4
3      1             10
4      1             12
5      2             1
6      2             5
7      2             9
8      2             13
9      3             3
10     3             7
11     3             15
12     4             3
13     4             6
14     4             14
15     5             2
16     5             7
17     6             2
18     6             8

I would like to get the result set like this

ProductName     Type        DIMMType       Color      Speed      FormFactor
Memory1         DRAM        Buffered       NULL       2300Mhz    DIMM
Memory2         DRAM        Unbuffered     NULL       2000Mhz    SODIMM
Headset1        Headset     NULL           White      NULL       On-Ear
Headset2        Headset     NULL           Red        NULL       Earbud
Keyboard1       Keyboard    NULL           White      NULL       NULL
Keyboard2       Keyboard    NULL           Yellow     NULL       NULL

Basically, I would like to be able to get all the products. How do I this without making function calls? My database is Microsoft SQL 2016

I have tried this, but it does not seem to work as expected because of those NULL values

Select p.ProductID, p.ProductNumber
 , lType.Value AS ProductType, lDimm.Value AS DimmType, lSpeed.Value AS Speed, lColor.Value AS Color, lFactor.Value AS FormFactor
From PRODUCT p
Left Join PRODUCT_FEATURES xType On p.ProductID = xType.ProductID
Inner Join LOOKUP lType On xType.LookupID = lType.LookupID
Inner Join LOOKUP_CATEGORY lcType On lType.CategoryID = lcType.LookupCategoryID And lcType.Category = 'Product Type'
Left Join PRODUCT_FEATURES xDimm On p.ProductID = xDimm.ProductID
Inner Join LOOKUP lDimm On xDimm.LookupID = lDimm.LookupID
Inner Join LOOKUP_CATEGORY lcDimm On lDimm.CategoryID = lcDimm.LookupCategoryID And lcDimm.Category = 'DIMM Type'
Left Join PRODUCT_FEATURES xSpeed On p.ProductID = xSpeed.ProductID
Inner Join LOOKUP lSpeed On xSpeed.LookupID = lSpeed.LookupID
Inner Join LOOKUP_CATEGORY lcSpeed On lSpeed.CategoryID = lcSpeed.LookupCategoryID And lcSpeed.Category = 'Speed'
Left Join PRODUCT_FEATURES xColor On p.ProductID = xColor.ProductID
Inner Join LOOKUP lColor On xColor.LookupID = lColor.LookupID
Inner Join LOOKUP_CATEGORY lcColor On lColor.CategoryID = lcColor.LookupCategoryID And lcColor.Category = 'Color'
Left Join PRODUCT_FEATURES xFactor On p.ProductID = xFactor.ProductID
Inner Join LOOKUP lFactor On xFactor.LookupID = lFactor.LookupID
Inner Join LOOKUP_CATEGORY lcFactor On lFactor.CategoryID = lcFactor.LookupCategoryID And lcFactor.Category = 'Form Factor'

LukStorms · Answer 1 · 2018-08-01T19:52:37.040

Grouping might be able to simplify it a bit.

SELECT
f.ProductID, 
p.ProductName, 
MAX(CASE WHEN lc.Category = 'Product Type' THEN l.Value END) AS ProductType, 
MAX(CASE WHEN lc.Category = 'DIMM Type' THEN l.Value END) AS DimmType, 
MAX(CASE WHEN lc.Category = 'Speed' THEN l.Value END) AS Speed, 
MAX(CASE WHEN lc.Category = 'Color' THEN l.Value END) AS Color, 
MAX(CASE WHEN lc.Category = 'Form Factor' THEN l.Value END) AS FormFactor
FROM PRODUCT_FEATURES f 
LEFT JOIN PRODUCT p ON p.ID = f.ProductID
LEFT JOIN LOOKUP l ON l.ID = f.LookupID
LEFT JOIN LOOKUP_CATEGORY lc ON lc.ID = l.CategoryID
GROUP BY f.ProductID, p.ProductName

score 0 · Accepted Answer · answered Aug 01 '18 at 21:00

You could also write it as below, if you want it truely to be dynamic you'd have to generate the statement below using dynamic SQL techniques, see SQL Server dynamic PIVOT query?:

Select ProductId,
ProductName,
Test.[Product type],
Test.[DIMM Type],
Test.[Speed],
Test.[Color],
Test.[Form Factor]

 From

(SELECT 
P.ProductID, 
p.ProductName, 
Category,
value

FROM imp.PRODUCT_FEATURES f 
LEFT JOIN imp.PRODUCT p ON p.productID = f.ProductID
LEFT JOIN imp.LOOKUP l ON l.lookupID = f.LookupID
LEFT JOIN imp.LOOKUP_CATEGORY lc ON lc.lookupcategoryID = l.CategoryID
) dt

pivot (max(dt.[value]) for dt.[CATEGORY] in ([Product type],[DIMM Type],[Speed],[Color],[Form Factor])) as test

For convenience.. --Create the schema IMP first..

/****** Object:  Table [imp].[Lookup]    Script Date: 8/1/2018 1:48:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [imp].[Lookup](
    [lookupID] [float] NULL,
    [CategoryID] [float] NULL,
    [Value] [nvarchar](255) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [imp].[Lookup_category]    Script Date: 8/1/2018 1:48:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [imp].[Lookup_category](
    [lookupcategoryID] [float] NULL,
    [Category] [nvarchar](255) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [imp].[Product]    Script Date: 8/1/2018 1:48:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [imp].[Product](
    [ProductID] [float] NULL,
    [ProductName] [nvarchar](255) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [imp].[Product_features]    Script Date: 8/1/2018 1:48:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [imp].[Product_features](
    [ID] [float] NULL,
    [ProductID] [float] NULL,
    [LookupID] [float] NULL
) ON [PRIMARY]
GO
INSERT [imp].[Lookup] ([lookupID], [CategoryID], [Value]) VALUES (1, 1, N'DRAM')
GO
INSERT [imp].[Lookup] ([lookupID], [CategoryID], [Value]) VALUES (2, 1, N'Keyboard')
GO
INSERT [imp].[Lookup] ([lookupID], [CategoryID], [Value]) VALUES (3, 1, N'Headset')
GO
INSERT [imp].[Lookup] ([lookupID], [CategoryID], [Value]) VALUES (4, 2, N'Buffered')
GO
INSERT [imp].[Lookup] ([lookupID], [CategoryID], [Value]) VALUES (5, 2, N'Unbuffered')
GO
INSERT [imp].[Lookup] ([lookupID], [CategoryID], [Value]) VALUES (6, 3, N'Red')
GO
INSERT [imp].[Lookup] ([lookupID], [CategoryID], [Value]) VALUES (7, 3, N'White')
GO
INSERT [imp].[Lookup] ([lookupID], [CategoryID], [Value]) VALUES (8, 3, N'Yellow')
GO
INSERT [imp].[Lookup] ([lookupID], [CategoryID], [Value]) VALUES (9, 4, N'2000Mhz')
GO
INSERT [imp].[Lookup] ([lookupID], [CategoryID], [Value]) VALUES (10, 4, N'2300Mhz')
GO
INSERT [imp].[Lookup] ([lookupID], [CategoryID], [Value]) VALUES (11, 4, N'2600Mhz')
GO
INSERT [imp].[Lookup] ([lookupID], [CategoryID], [Value]) VALUES (12, 5, N'DIMM')
GO
INSERT [imp].[Lookup] ([lookupID], [CategoryID], [Value]) VALUES (13, 5, N'SODIMM')
GO
INSERT [imp].[Lookup] ([lookupID], [CategoryID], [Value]) VALUES (14, 5, N'Earbud')
GO
INSERT [imp].[Lookup] ([lookupID], [CategoryID], [Value]) VALUES (15, 5, N'On-Ear')
GO
INSERT [imp].[Lookup_category] ([lookupcategoryID], [Category]) VALUES (1, N'Product Type')
GO
INSERT [imp].[Lookup_category] ([lookupcategoryID], [Category]) VALUES (2, N'DIMM Type')
GO
INSERT [imp].[Lookup_category] ([lookupcategoryID], [Category]) VALUES (3, N'Color')
GO
INSERT [imp].[Lookup_category] ([lookupcategoryID], [Category]) VALUES (4, N'Speed')
GO
INSERT [imp].[Lookup_category] ([lookupcategoryID], [Category]) VALUES (5, N'Form Factor')
GO
INSERT [imp].[Product] ([ProductID], [ProductName]) VALUES (1, N'Memory1')
GO
INSERT [imp].[Product] ([ProductID], [ProductName]) VALUES (2, N'Memory2')
GO
INSERT [imp].[Product] ([ProductID], [ProductName]) VALUES (3, N'Headset1')
GO
INSERT [imp].[Product] ([ProductID], [ProductName]) VALUES (4, N'Headset2')
GO
INSERT [imp].[Product] ([ProductID], [ProductName]) VALUES (5, N'Keyboard1')
GO
INSERT [imp].[Product] ([ProductID], [ProductName]) VALUES (6, N'Keyboard2')
GO
INSERT [imp].[Product_features] ([ID], [ProductID], [LookupID]) VALUES (1, 1, 1)
GO
INSERT [imp].[Product_features] ([ID], [ProductID], [LookupID]) VALUES (2, 1, 4)
GO
INSERT [imp].[Product_features] ([ID], [ProductID], [LookupID]) VALUES (3, 1, 10)
GO
INSERT [imp].[Product_features] ([ID], [ProductID], [LookupID]) VALUES (4, 1, 12)
GO
INSERT [imp].[Product_features] ([ID], [ProductID], [LookupID]) VALUES (5, 2, 1)
GO
INSERT [imp].[Product_features] ([ID], [ProductID], [LookupID]) VALUES (6, 2, 5)
GO
INSERT [imp].[Product_features] ([ID], [ProductID], [LookupID]) VALUES (7, 2, 9)
GO
INSERT [imp].[Product_features] ([ID], [ProductID], [LookupID]) VALUES (8, 2, 13)
GO
INSERT [imp].[Product_features] ([ID], [ProductID], [LookupID]) VALUES (9, 3, 3)
GO
INSERT [imp].[Product_features] ([ID], [ProductID], [LookupID]) VALUES (10, 3, 7)
GO
INSERT [imp].[Product_features] ([ID], [ProductID], [LookupID]) VALUES (11, 3, 15)
GO
INSERT [imp].[Product_features] ([ID], [ProductID], [LookupID]) VALUES (12, 4, 3)
GO
INSERT [imp].[Product_features] ([ID], [ProductID], [LookupID]) VALUES (13, 4, 6)
GO
INSERT [imp].[Product_features] ([ID], [ProductID], [LookupID]) VALUES (14, 4, 14)
GO
INSERT [imp].[Product_features] ([ID], [ProductID], [LookupID]) VALUES (15, 5, 2)
GO
INSERT [imp].[Product_features] ([ID], [ProductID], [LookupID]) VALUES (16, 5, 7)
GO
INSERT [imp].[Product_features] ([ID], [ProductID], [LookupID]) VALUES (17, 6, 2)
GO
INSERT [imp].[Product_features] ([ID], [ProductID], [LookupID]) VALUES (18, 6, 8)
GO

How to query SQL dynamic lookup

2 Answers2