Pivot concern

Transact-SQL
1

Hello Techie,

May i get help on this critical scenario.

if any of the value not matched the crieteria mention beside column name , required to be in output. and output should look like picture given below.

if value meeting the crieteria, then result column would be blank. i am looking for only corrupt data and its associated attribute.

Table 1

Table 2

and Result should look like this.

Result:

d8d4db2fd4cc17cd16e7cdc4fadf61f40078.png" width="690" height="125">

Table 2

and Result should look like this.

Result:

table Structure.

DECLARE @TABLE1 TABLE
(
SKU VARCHAR (50),
Diameter VARCHAR (50),
Diameter_value VARCHAR (50),--value Must be <30 and non-blank,
Width VARCHAR (50),
Width_Value VARCHAR (50),-- Width Value (No Blank allowed if its there then it should be in output)
Bolt_Pattern VARCHAR (50),
Bolt_Pattern_Value VARCHAR (50) -- IF Value NOT match with Bolt TABLE -- table 2
)

DECLARE @TABLE2 TABLE
(
BOLT VARCHAR (50)
)

insert @TABLE1

SELECT 'AMR105C-816158', 'Diameter', '18', 'Width','', 'Bolt_Pattern', '5x120.7' UNION ALL
SELECT 'AMR121C-786149', 'Diameter', '65', 'Width','8.5','Bolt_Pattern','5x120.799999' UNION ALL
SELECT 'AMRAR10428536NC', 'Diameter', '20', 'Width','8.5', 'Bolt_Pattern', '6x135' UNION ALL
SELECT 'AMRAR10428538A', 'Diameter', '85', 'Width','', 'Bolt_Pattern', '6x139.7' UNION ALL
SELECT 'AMRAR10428538NC', 'Diameter','', 'Width','8.5', 'Bolt_Pattern', '86x139.85555'

INSERT @TABLE2

SELECT '5x120.7' UNION ALL
SELECT '6x135' UNION ALL
SELECT '6x139.7'

SELECT * FROM @TABLE1
SELECT * FROM @TABLE2

Thank You Very Much.

2

Please post your data as:

  1. CREATE TABLE statements for each table invovled
  2. INSERT INTO statements to populate the tables
3

Hi gbritton.

i have posted data as below.
Thank you very much for help.

/*
if any of the value not matching the crieteria mention in the comment , those should be in output. looking for only corrupt data and its associated attribute.
*/

DECLARE @TABLE1 TABLE
(
SKU VARCHAR (50),
Diameter VARCHAR (50),
Diameter_value VARCHAR (50),--value Must be <30 and non-blank,
Width VARCHAR (50),
Width_Value VARCHAR (50),-- Width Value (No Blank allowed if its there then it should be in output)
Bolt_Pattern VARCHAR (50),
Bolt_Pattern_Value VARCHAR (50) -- IF Value NOT match with Bolt TABLE -- table 2
)

DECLARE @TABLE2 TABLE
(
BOLT VARCHAR (50)
)

insert @TABLE1

SELECT 'AMR105C-816158', 'Diameter', '18', 'Width','', 'Bolt_Pattern', '5x120.7' UNION ALL
SELECT 'AMR121C-786149', 'Diameter', '65', 'Width','8.5','Bolt_Pattern','5x120.799999' UNION ALL
SELECT 'AMRAR10428536NC', 'Diameter', '20', 'Width','8.5', 'Bolt_Pattern', '6x135' UNION ALL
SELECT 'AMRAR10428538A', 'Diameter', '85', 'Width','', 'Bolt_Pattern', '6x139.7' UNION ALL
SELECT 'AMRAR10428538NC', 'Diameter','', 'Width','8.5', 'Bolt_Pattern', '86x139.85555'

INSERT @TABLE2

SELECT '5x120.7' UNION ALL
SELECT '6x135' UNION ALL
SELECT '6x139.7'

SELECT * FROM @TABLE1
SELECT * FROM @TABLE2

Thanks again.

4

Good Morning GBritton,
Please help me.

5

SELECT a.SKU,
case when a.Diameter_value> 30 or a.Diameter_value = '' then a.Diameter else '' END as Diameter,
case when a.Diameter_value> 30 or a.Diameter_value = '' then a.Diameter_value else '' end Diameter_value,
case when a.width_value = '' then a.Width else '' end width,
case when a.width_value = '' then a.Width_Value else '' end width_value,
case when b.BOLT is null then a.Bolt_Pattern else '' end Bolt_Pattern,
case when b.BOLT is null then a.Bolt_Pattern_Value else '' end Bolt_Pattern_Value
FROM @TABLE1 a
left outer join @TABLE2 b
on a.Bolt_Pattern_Value = b.BOLT