CODEWITHSUNDEEP
sqljoinsql-server-2012
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6950

SQL joins with tables that contain dimensions

How to join table1 and table2 (containing dimensions and their values) to get the following results: enter image description here

Update Thank you for proposing the way with PIVOT. Just for curiosity - is it possible to solve it by several left joins?

Update 2 I would like to share with you my idea of solving the problem with several left joins. It is very simple and much faster than Pivot way. Below you may find my answer.

Upvotes: 1

Views: 772

Answers (4)

Przemyslaw Remin
Przemyslaw Remin

Reputation: 6950

My experience has worked out this code which I want to share with you. The key is to assign new alias to dimension table every time we make a left join. 

SELECT 
    T1.ID
    ,S.DimensionValue AS Sex
    ,E.DimensionValue AS Eyes
    ,WT.DimensionValue AS [Worker Type]
FROM #Table1 AS T1

LEFT JOIN #Table2 AS S  -- new alias here for dimension table
ON T1.ID=S.ID AND S.Dimension='sex'
LEFT JOIN #Table2 AS E  -- new alias here for dimension table
ON T1.ID=E.ID AND E.Dimension='eyes'
LEFT JOIN #Table2 AS WT -- new alias here for dimension table
ON T1.ID=WT.ID AND WT.Dimension='worker type'

Upvotes: 0

Swapnil
Swapnil

Reputation: 434

using left joins:

CREATE TABLE #Table1
(
    ID int, 
    Salary int
)

INSERT INTO #Table1
VALUES 
(1, 1000),
(2, 2000)

CREATE TABLE #Table2
(
    ID int, 
    Dimension varchar(1000),
    DimensionValue varchar(1000)
)

INSERT INTO #Table2 
VALUES
(1, 'eyes','blue'),
(1, 'sex','male'),
(1, 'worker type','marvelous'),
(2, 'eyes','brown'),
(2, 'sex', 'female'),
(2, 'worker type','spectacular')


SELECT T1.ID,S.Sex,E.Eyes,WT.[Worker Type]
FROM #Table1 AS T1
LEFT JOIN
(   SELECT  ID,
            DimensionValue AS Sex
    FROM #Table2
    WHERE Dimension='sex'
) S ON T1.ID=S.ID
LEFT JOIN
(   SELECT  ID,
            DimensionValue AS Eyes
    FROM #Table2
    WHERE Dimension='eyes'
) E ON T1.ID=E.ID
LEFT JOIN
(   SELECT  ID,
            DimensionValue AS [Worker Type]
    FROM #Table2
    WHERE Dimension='worker type'
) WT ON T1.ID=WT.ID

DROP TABLE #Table1,#Table2

have to mention left join for each dimension..!!

Upvotes: 1

ASh
ASh

Reputation: 35720

declare @table1 table
(
    id int, 
    salary int
)

insert into @table1
values 
(1, 1000),
(2, 2000)

declare @table2 table
(
    id int, 
    dimension varchar(1000),
    dimensionValue varchar(1000)
)

insert into @table2 
values
(1, 'eyes','blue'),
(1, 'sex','male'),
(1, 'worker type','marvelous'),
(2, 'eyes','brown'),
(2, 'sex', 'female'),
(2, 'worker type','spectacular')

query with pivot

SELECT t2.*, t1.salary
FROM
@table1 t1 
join 
    (SELECT * FROM @table2) AS SourceTable
    PIVOT
    (
        max(dimensionValue)
        FOR dimension IN ([eyes],[worker type],[sex])
    ) AS T2 
on T2.id = t1.id

Upvotes: 3

Swapnil
Swapnil

Reputation: 434

Try below code:

WITH PivotTable2 AS
(
    SELECT ID, SEX, EYES, [WORKER TYPE]
    FROM (
    SELECT ID, DIMENSION, [DIMENSION VALUE]
    FROM Table2) up
    PIVOT (MAX([DIMENSION VALUE]) FOR DIMENSION IN (SEX, EYES, [WORKER TYPE])) AS pvt
)
SELECT T1.ID,T1.Salary,T2.SEX,T2.EYES,T2.[WORKER TYPE]
FROM Table1 AS T1
LEFT JOIN PivotTable2 T2 ON T1.ID=T2.ID

Hoep this wil help!

Upvotes: 2

Related Questions