Alias column with Pivot

Transact-SQL
1

Hello forum,

May anyone please help me on below scenerio.

/*
There are three tables
Table 1: Main
Table 2: BoltReference
table 3: LugReference

Each column is having rule, if any data violate the rule, it should be appear in result column

Rules

Diameter Looks For Blanks OR number values greater than 28 or less then 6 in this column
Width Looks For Blanks OR number values greater than 16 or less then 3 in this column
Bolt1 Looks For Blanks or items that do not match the supplied Bolt Reference Table
Bolt2 Looks For items that do not match the supplied Bolt Reference Table (blanks '' are OK)
Bolt3 Looks For items that do not match the supplied Bolt Reference Table (blanks '' are OK)
Core Looks For Blanks or items that do not have two decimal places
Offset Looks for blanks or Plus Sign (+) or numeric values greater then 125
Lug Looks For Blanks or items that do not match the supplied Lug Reference Table

Outputs

Alias column Result 1 has value whenever there is invalid data in Diameter column else blank
Alias column Result 2 has value whenever there is invalid data in width column else blank
Alias column Result 3 has value whenever there is invalid data in Bolt1 column else blank
Alias column Result 4 has value whenever there is invalid data in Bolt2 column else blank
Alias column Result 5 has value whenever there is invalid data in Bolt3 column else blank
Alias column Result 6 has value whenever there is invalid data in Core column else blank
Alias column Result 7 has value whenever there is invalid data in Offset column else blank
Alias column Result 8 has value whenever there is invalid data in Lug column else blank

Table structure

DECLARE @MAIN TABLE
(
SKU VARCHAR (50),
Diameter INT,
Width FLOAT,
Bolt1 VARCHAR (20),
Bolt2 VARCHAR (20),
Bolt3 VARCHAR (20),
Core FLOAT,
Offset CHAR (10),
Lug VARCHAR (30)

)

INSERT @MAIN

SELECT 'R105C', '18','', '121X','','','70.70','+58','Acorn' UNION ALL
SELECT 'R121C', '2','8.5','125X','','', '70.7','49','Duplex' UNION ALL
SELECT 'RAR10', '20','8.5','135X','555XX','','87.10','','Check me'

DECLARE @BoltReference TABLE
(
Bolt1Pattern VARCHAR (20),
Bolt2Pattern VARCHAR (20),
Bolt3Pattern VARCHAR (20)

)

Insert @BoltReference
select '184X','100','84' UNION ALL
select '121X','129','213' UNION ALL
select '135X','131','410' union all
SELECT '10x184','10x184','6x70' UNION ALL
SELECT '303x112','8x40','3x112' UNION ALL
SELECT '8x100','4x100','8x65'

DEClare @LugReference table
(
lug varchar (30)
)
insert @LugReference
SELECT 'Acorn' UNION ALL
SELECT 'Duplex' UNION ALL
SELECT 'OpenEnd'

and output look like

Pasted image868×81 13.6 KB

Thanks a lot for your support.

2

You can just create a series of CASE statements with each applying your set of rules.

Below should satisfy those requirements with a couple of caveats:

  1. Bolt Values have X appended to the end, but the lookup table does not, so removed them in the cross apply

  2. Quick google found this as an interesting approaching to counting decimals using math functions - FLOOR(LOG10(REVERSE(ABS(M.Core)+1)))+1.

    SELECT M.SKU,
    M.Diameter,
    M.Width,
    M.Bolt1,
    M.Bolt2,
    M.Bolt3,
    M.Core,
    M.Offset,
    M.Lug,
    Result1 = CASE WHEN M.Diameter IS NULL OR M.Diameter > 28 OR M.Diameter < 6 THEN 'Diameter' END,
    Result2 = CASE WHEN M.Width IS NULL OR M.Width > 16 OR M.Width < 3 THEN 'Width' END,
    Result3 = CASE WHEN NOT EXISTS(SELECT 1 FROM @BoltReference AS BR WHERE CA1.Bolt1WithoutTrailingX = BR.Bolt1Pattern) THEN 'Bolt1' END,
    Result4 = CASE WHEN NOT EXISTS(SELECT 1 FROM @BoltReference AS BR WHERE CA1.Bolt2WithoutTrailingX = BR.Bolt2Pattern) AND M.Bolt2 <> '' THEN 'Bolt2' END,
    Result5 = CASE WHEN NOT EXISTS(SELECT 1 FROM @BoltReference AS BR WHERE CA1.Bolt3WithoutTrailingX = BR.Bolt3Pattern) AND M.Bolt3 <> '' THEN 'Bolt3' END,
    Result6 = CASE WHEN M.Core IS NULL OR FLOOR(LOG10(REVERSE(ABS(M.Core)+1)))+1 <> 2 THEN 'Core' END,
    Result6 = CASE WHEN M.Offset > 125 OR M.Offset = '' OR M.Offset IS NULL OR LEFT(M.Offset,1) = '+' THEN 'Offset' END,
    Result8 = CASE WHEN NOT EXISTS(SELECT 1 FROM @LugReference AS LR WHERE M.Lug = LR.lug) THEN 'Lug' END
    FROM @MAIN AS M
    CROSS
    APPLY (SELECT REPLACE(M.Bolt1,'X',''),
    REPLACE(M.Bolt2,'X',''),
    REPLACE(M.Bolt3,'X','')
    ) AS CA1(Bolt1WithoutTrailingX,Bolt2WithoutTrailingX,Bolt3WithoutTrailingX);

1 Like
3

What I have done in this case is to use the PIVOT command to get the key columns pivoted in a temp table, then use those columns to add columns for the rest of the data. When I have related data and few columns, the CASE statement is easy to fill the additional columns with, but if there are 'many' columns, I build a temp table for each column to collect the other fields in one place so that speed is not reduced.

1 Like
4

Thanks a lot Sir Dohsan....... :smile: A great function FLOOR, and best use of it learn today.

5

Hi Doshan, may i ask one more question.its a part of same question. now we are checking
Result6 = CASE WHEN M.Core IS NULL OR FLOOR(LOG10(REVERSE(ABS(M.Core)+1)))+1 <> 2 THEN 'Core' END,,

is it possible to check two more condition in Result6

if value in Bolt1, Bolt2 and Bol3 is like

if 8 present before x and value less than 95 after x then it should also appear in output
if 6 present before x and value less than 71 after x then it should also appear in output

Please suggest. Thanks a ton...

6

Hi Doshan,

can we include the below check in Result 6

SELECT Bolt1 FROM @BoltReference
WHERE (CAST(SUBSTRING(Bolt1, 1, CHARINDEX('x', Bolt1) - 1) AS SMALLINT) = 6
AND CAST(SUBSTRING(Bolt1, CHARINDEX('x', Bolt1) + 1, LEN(Bolt1) -
CHARINDEX('x', Bolt1)) AS SMALLINT) < 71)
OR
(CAST(SUBSTRING(Bolt1, 1, CHARINDEX('x', Bolt1) - 1) AS SMALLINT) = 8 AND
CAST(SUBSTRING(Bolt1, CHARINDEX('x', Bolt1) + 1, LEN(Bolt1) - CHARINDEX('x',
Bolt1)) AS SMALLINT) < 95)

UNION
SELECT Bolt2 FROM @BoltReference
WHERE (CAST(SUBSTRING(Bolt2, 1, CHARINDEX('x', Bolt2) - 1) AS SMALLINT) = 6
AND CAST(SUBSTRING(Bolt2, CHARINDEX('x', Bolt2) + 1, LEN(Bolt2) -
CHARINDEX('x', Bolt2)) AS SMALLINT) < 71)
OR
(CAST(SUBSTRING(Bolt2, 1, CHARINDEX('x', Bolt2) - 1) AS SMALLINT) = 8 AND
CAST(SUBSTRING(Bolt2, CHARINDEX('x', Bolt2) + 1, LEN(Bolt2) - CHARINDEX('x',
Bolt2)) AS SMALLINT) < 95)
UNION
SELECT Bolt3 FROM @BoltReference

WHERE (CAST(SUBSTRING(Bolt3, 1, CHARINDEX('x', Bolt3) - 1) AS SMALLINT) = 6
AND CAST(SUBSTRING(Bolt3, CHARINDEX('x', Bolt3) + 1, LEN(Bolt3) -
CHARINDEX('x', Bolt3)) AS SMALLINT) < 71)
OR
(CAST(SUBSTRING(Bolt3, 1, CHARINDEX('x', Bolt3) - 1) AS SMALLINT) = 8 AND
CAST(SUBSTRING(Bolt3, CHARINDEX('x', Bolt3) + 1, LEN(Bolt3) - CHARINDEX('x',
Bolt3)) AS SMALLINT) < 95)

Please suggest.