-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathDW SQL CreateProductView
63 lines (52 loc) · 4.17 KB
/
DW SQL CreateProductView
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
SELECT
a.[FieldValueProductId] as ProductId,
b.[ProductNumber],
b.[ProductName],
d.[FieldValueValue] AS Supplier,
e.[FieldValueValue] AS [Range],
f.[FieldValueValue] AS [Type],
g.[FieldValueValue] AS FrameType,
h.[FieldValueValue] AS StructureMaterial,
i.[FieldValueValue] AS SeatFinish,
j.[FieldValueValue] AS BackFinish,
k.[FieldValueValue] AS SeatHeight,
l.[FieldValueValue] AS TopFinish,
m.[FieldValueValue] AS TopShape
FROM (SELECT Distinct [FieldValueProductId]
FROM [JRFRapido].[dbo].[EcomProductCategoryFieldValue]
WHERE [FieldValueFieldCategoryId] <> 'FabricSuppliers' AND [FieldValueFieldCategoryId] <> 'LeatherSuppliers') as a /*[ProductId]*/
inner JOIN
(SELECT DISTINCT [ProductId],[ProductNumber],[ProductName] FROM [JRFRapido].[dbo].[EcomProducts]) AS b /*[Supplier]*/
ON a.[FieldValueProductId] = b.[ProductId]
JOIN
(SELECT DISTINCT [FieldValueProductId],[FieldValueValue] FROM [EcomProductCategoryFieldValue] WHERE [FieldValueFieldId] = 'Supplier' AND [FieldValueFieldCategoryId] <> 'FabricSuppliers' AND [FieldValueFieldCategoryId] <> 'LeatherSuppliers') AS d /*[Supplier]*/
ON a.[FieldValueProductId] = d.[FieldValueProductId]
FULL OUTER JOIN
(SELECT DISTINCT [FieldValueProductId],[FieldValueValue] FROM [EcomProductCategoryFieldValue] WHERE [FieldValueFieldId] = 'Range' AND [FieldValueFieldCategoryId] <> 'FabricSuppliers' AND [FieldValueFieldCategoryId] <> 'LeatherSuppliers') AS e /*[Range]*/
ON a.[FieldValueProductId] = e.[FieldValueProductId]
FULL OUTER JOIN
(SELECT DISTINCT [FieldValueProductId],[FieldValueValue] FROM [EcomProductCategoryFieldValue] WHERE [FieldValueFieldId] = 'Type' AND [FieldValueFieldCategoryId] <> 'FabricSuppliers' AND [FieldValueFieldCategoryId] <> 'LeatherSuppliers') AS f /*[Type]*/
ON a.[FieldValueProductId] = f.[FieldValueProductId]
FULL OUTER JOIN
(SELECT DISTINCT [FieldValueProductId],[FieldValueValue] FROM [EcomProductCategoryFieldValue] WHERE [FieldValueFieldId] = 'FrameType' AND [FieldValueFieldCategoryId] <> 'FabricSuppliers' AND [FieldValueFieldCategoryId] <> 'LeatherSuppliers') AS g /*[FrameType]*/
ON a.[FieldValueProductId] = g.[FieldValueProductId]
FULL OUTER JOIN
(SELECT DISTINCT [FieldValueProductId],[FieldValueValue] FROM [EcomProductCategoryFieldValue] WHERE [FieldValueFieldId] = 'StructureMaterial' AND [FieldValueFieldCategoryId] <> 'FabricSuppliers' AND [FieldValueFieldCategoryId] <> 'LeatherSuppliers') AS h /*[StructureMaterial]*/
ON a.[FieldValueProductId] = h.[FieldValueProductId]
FULL OUTER JOIN
(SELECT DISTINCT [FieldValueProductId],[FieldValueValue] FROM [EcomProductCategoryFieldValue] WHERE [FieldValueFieldId] = 'SeatFinish' AND [FieldValueFieldCategoryId] <> 'FabricSuppliers' AND [FieldValueFieldCategoryId] <> 'LeatherSuppliers') AS i /*[SeatFinish]*/
ON a.[FieldValueProductId] = i.[FieldValueProductId]
FULL OUTER JOIN
(SELECT DISTINCT [FieldValueProductId],[FieldValueValue] FROM [EcomProductCategoryFieldValue] WHERE [FieldValueFieldId] = 'BackFinish' AND [FieldValueFieldCategoryId] <> 'FabricSuppliers' AND [FieldValueFieldCategoryId] <> 'LeatherSuppliers') AS j /*[BackFinish]*/
ON a.[FieldValueProductId] = j.[FieldValueProductId]
FULL OUTER JOIN
(SELECT DISTINCT [FieldValueProductId],[FieldValueValue] FROM [EcomProductCategoryFieldValue] WHERE [FieldValueFieldId] = 'SeatHeight' AND [FieldValueFieldCategoryId] <> 'FabricSuppliers' AND [FieldValueFieldCategoryId] <> 'LeatherSuppliers') AS k /*[SeatHeight]*/
ON a.[FieldValueProductId] = k.[FieldValueProductId]
FULL OUTER JOIN
(SELECT DISTINCT [FieldValueProductId],[FieldValueValue] FROM [EcomProductCategoryFieldValue] WHERE [FieldValueFieldId] = 'TopFinish' AND [FieldValueFieldCategoryId] <> 'FabricSuppliers' AND [FieldValueFieldCategoryId] <> 'LeatherSuppliers') AS l /*[TopFinish]*/
ON a.[FieldValueProductId] = l.[FieldValueProductId]
FULL OUTER JOIN
(SELECT DISTINCT [FieldValueProductId],[FieldValueValue] FROM [EcomProductCategoryFieldValue] WHERE [FieldValueFieldId] = 'TopShape' AND [FieldValueFieldCategoryId] <> 'FabricSuppliers' AND [FieldValueFieldCategoryId] <> 'LeatherSuppliers') AS m /*[TopShape]*/
ON a.[FieldValueProductId] = m.[FieldValueProductId]
WHERE A.FieldValueProductId is not null and A.FieldValueProductId <> 'NULL'
order by a.FieldValueProductId