Sql-server – select items from multiple rows and add to one

rowsql-server-2008-r2

I need some help on SQL as this kind of selecting is beyond my knowledge. The result of the select should be in one row as it is shown in the picture. Can someone provide any ideas on how to achieve this?

enter image description here

if col_name1 =AA -> add col_name2 as value col_name5

if col_name1 =BB -> add col_name3 value as col_name6

if col_name1 =CC -> add col_name4 value of first CC (c31) as col_name7 and col_name4 of second CC (c31) value as col_name8

original table might not have all 4 ids

Best Answer

I'm making some assumptions about how your data is ordered (do all BB's come after AA's ordered by Id; do all CC's come after BB's ordered by Id), etc. You also don't say what you want for the ID column in the final result, so I didn't include that column.

By using a Common Table Expression and the Row_Number Window Function, I'm added a sequential row number to each of your original rows.

Here's a demo set up script:

DECLARE @Data TABLE (
    ID INT
    ,col_name1 VARCHAR(10)
    ,col_name2 VARCHAR(10)
    ,col_name3 VARCHAR(10)
    ,col_name4 VARCHAR(10)
    )
INSERT INTO @Data (ID, col_name1, col_name2, col_name3, col_name4) VALUES
(1,'AA','a1','a2','a3'),
(2,'BB','b1','b2','b3'),
(3,'CC','c1','c2','c31'),
(4,'CC','c1','c2','c32'),
(5,'CC','c1','c2','c33'),
(6,'CC','c1','c2','c34')
;
with DataAndRowNumber as
(
select *,ROW_NUMBER () over(order by ID) as rn
from @Data
)
Select * from DataAndRowNumber

produces 

| ID | col_name1 | col_name2 | col_name3 | col_name4 | rn |
|----|-----------|-----------|-----------|-----------|----|
| 1  | AA        | a1        | a2        | a3        | 1  |
| 2  | BB        | b1        | b2        | b3        | 2  |
| 3  | CC        | c1        | c2        | c31       | 3  |
| 4  | CC        | c1        | c2        | c32       | 4  |
| 5  | CC        | c1        | c2        | c33       | 5  |
| 6  | CC        | c1        | c2        | c34       | 6  |

From your question, it appears that you want col_name7 to be the first CC value, col_name8 to be the next CC value, col_name9 to be the next CC value and col_name10 to be the last CC value. I'm assuming there will always be an 'AA' and a 'BB', so the first CC row is row number 3, the next CC row will be 4 and so on.

Putting it all together

DECLARE @Data TABLE (
    ID INT
    ,col_name1 VARCHAR(10)
    ,col_name2 VARCHAR(10)
    ,col_name3 VARCHAR(10)
    ,col_name4 VARCHAR(10)
    )
INSERT INTO @Data (ID, col_name1, col_name2, col_name3, col_name4) VALUES
(1,'AA','a1','a2','a3'),
(2,'BB','b1','b2','b3'),
(3,'CC','c1','c2','c31'),
(4,'CC','c1','c2','c32'),
(5,'CC','c1','c2','c33'),
(6,'CC','c1','c2','c34')
;
with DataAndRowNumber as
(
select *,ROW_NUMBER () over(order by ID) as rn
from @Data
)
select distinct
    (select col_name2 from DataAndRowNumber where rn=1) as col_name5,
    (select col_name2 from DataAndRowNumber where rn=2) as col_name6,
    (select col_name4 from DataAndRowNumber where rn=3) as col_name7,
    (select col_name4 from DataAndRowNumber where rn=4) as col_name8,
    (select col_name4 from DataAndRowNumber where rn=5) as col_name9,
    (select col_name4 from DataAndRowNumber where rn=6) as col_name10
from DataAndRowNumber

| col_name5 | col_name6 | col_name7 | col_name8 | col_name9 | col_name10 |
|-----------|-----------|-----------|-----------|-----------|------------|
| a1        | b1        | c31       | c32       | c33       | c34        |
Related Question