SQL Server : Making 2 rows value into 1 column and joining it, while using a where clause

Question

I am using an SQL Server database and have these following tables

Table "Data"

   ------------------
   | Id | data_name |
   ------------------
   | 1  |Data 1     |
   | 2  |Data 2     |
   | 3  |Data 3     |
   | 4  |Data 4     |
   | 5  |Data 5     |
   ------------------

and Table "Value_data"

   --------------------------------------------------------------------------------------------------------------
   | Id | data_id   | date       | col_1_type | col_1_name | col_1_value | col_2_type | col_2_name | col_2_value |
   --------------------------------------------------------------------------------------------------------------
   | 1  | 1         | 2017-01-01 | A          | Alpha      | 12          | B          | Beta       | 23          |
   | 2  | 1         | 2017-02-01 | A          | Alpha      | 32          | B          | Beta       | 42          |
   ---------------------------------------------------------------------------------------------------------------

And i want to make result like so

   -----------------------------------------------------------------
   |value_id | data_id | data_name | date       | A-Alpha | B-Beta |
   -----------------------------------------------------------------
   |1        | 1       | Data 1    | 2017-01-01 | 12      | 23     |
   |2        | 1       | Data 1    | 2017-02-01 | 32      | 42     |
   -----------------------------------------------------------------

I've search multiple times for solutions, i've tried using Pivot for example, but it wont work well with the data that i'm using with the joining tables, anyone had a solution with the same case?

Don't post images for data and expected results. Always provide text data so that it is easier to manipulate database entries. Would you like if we answer the question in an image.? — Kaushik Nayak, Dec 21 '17 at 06:59
Possible duplicate of [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) — Zach, Dec 21 '17 at 07:01
actually, i already succeded in joining the table, its just that making the values into a column is what i'm struggling — Rama Ramadhan, Dec 21 '17 at 07:05
@KaushikNayak sorry.. i know its not ethical, but i need to find the answer ASAP, so i just made an html and feed it with random data, so i can go on to search for similiar issues :( — Rama Ramadhan, Dec 21 '17 at 07:07

score 1 · Answer 1 · answered Dec 21 '17 at 07:55

This looks like a basic left join

create table data( Id int, data_name varchar(20))
insert into data values
( 1  ,'Data 1'),     
( 2  ,'Data 2'),     
( 3  ,'Data 3'),     
( 4  ,'Data 4'),     
( 5  ,'Data 5')     

create table Value_data( Id int,  data_id   int, dt  smalldatetime, col_1_type varchar(1), col_1_name varchar(5), col_1_value int, col_2_type varchar(1), col_2_name varchar(5), col_2_value int)
insert into value_data values
( 1  , 1         , '2017-01-01' , 'A'          , 'Alpha'      , 12          , 'B'          , 'Beta'       , 23          ),
( 2  , 1         , '2017-02-01' , 'A'          , 'Alpha'      , 32          , 'B'          , 'Beta'       , 42          )

select d.id,vd.id,d.data_name,vd.dt,
        vd.col_1_value as 'Alpha',vd.col_2_value as 'Beta'
from data d
left join value_data vd on d.id = vd.data_id

id          id          data_name            dt                      Alpha       Beta
----------- ----------- -------------------- ----------------------- ----------- -----------
1           1           Data 1               2017-01-01 00:00:00     12          23
1           2           Data 1               2017-02-01 00:00:00     32          42
2           NULL        Data 2               NULL                    NULL        NULL
3           NULL        Data 3               NULL                    NULL        NULL
4           NULL        Data 4               NULL                    NULL        NULL
5           NULL        Data 5               NULL                    NULL        NULL

(6 row(s) affected)

score 1 · Accepted Answer · answered Dec 21 '17 at 07:59

You can use this.

DECLARE @Data TABLE ( Id INT, data_name VARCHAR(10) )
INSERT INTO @Data VALUES
( 1 ,'Data 1'),
( 2 ,'Data 2'),
( 3 ,'Data 3'),
( 4 ,'Data 4'),
( 5 ,'Data 5')

DECLARE @Value_data TABLE (Id INT, data_id INT, [date] DATE, col_1_type VARCHAR(10), col_1_name VARCHAR(10), col_1_value INT, col_2_type VARCHAR(10), col_2_name VARCHAR(10), col_2_value INT)
INSERT INTO @Value_data VALUES
( 1, 1, '2017-01-01','A','Alpha','12','B','Beta','23'),
( 2, 1, '2017-02-01','A','Alpha','32','B','Beta','42')

;WITH CTE AS (
    select vd.Id value_id
        , vd.data_id 
        , d.data_name 
        , vd.[date]
        , vd.col_1_type + '-' +vd.col_1_name Col1
        , vd.col_1_value
        , vd.col_2_type + '-' +vd.col_2_name Col2
        , vd.col_2_value
    from @Value_data vd
        inner join @Data d on vd.data_id = d.Id
)   
SELECT * FROM CTE 
    PIVOT( MAX(col_1_value) FOR Col1 IN ([A-Alpha])) PVT_A
    PIVOT( MAX(col_2_value) FOR Col2 IN ([B-Beta])) PVT_B

Result:

value_id    data_id     data_name  date       A-Alpha     B-Beta
----------- ----------- ---------- ---------- ----------- -----------
1           1           Data 1     2017-01-01 12          23
2           1           Data 1     2017-02-01 32          42

AWESOME ! this did the trick, thanks. though i need to make the select to be dynamic now, but its a big step for my solution. Big Thanks! — Rama Ramadhan, Dec 21 '17 at 08:48

Laxmi · Answer 3 · 2017-12-21T08:38:03.950

1

SELECT
  a.id,
  a.data_id,
  b.data_name,
  a.date1,
  a.col_1_value AS alpha,
  a.col_2_value AS beta
FROM table1 a WITH (NOLOCK)
INNER JOIN table2 b WITH (NOLOCK)
  ON a.data_id = b.id

edited Dec 21 '17 at 08:38

answered Dec 21 '17 at 08:10

Laxmi

3,830
26
30

SQL Server : Making 2 rows value into 1 column and joining it, while using a where clause

3 Answers3