select items from multiple rows and add to one

Question

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

• Scott Hodgin - Retired: What Relational database management system (RDBMS) are you using? SQL Server, Oracle, etc.? And what version? Like for SQL Server, 2008, 2012, etc.?
• vandit: I am using sql2008r2
• ypercubeᵀᴹ: What if there are 100 rows in the table? And say 50 have AA, 40 have BB and 10 have CC?
• vandit: there are max 6 rows; one AA, one BB and max 4 CC, there may be other rows with DD but are not important
• ypercubeᵀᴹ: The question was what should the result be? If there are 6 rows with AA/BB/CC, do you need 6 columns in the result?

Answer 1

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 |

• vandit: Partly is ok (if I could use.. where col_name1 = 'AA') Fill based on row number is not ok since there could be DD (which should not be in table) between any of intems - on second place for example. ID column doesn't need to be in result table.
• Scott Hodgin - Retired: Since you don't care about 'DD' rows, why can't you simply filter those out in the Common Table Expression? select *,ROW_NUMBER () over(order by ID) as rn from @Data where col_name1<>'DD'
• vandit: oh,yes, you' ar e right. Finally I tried to order by case when col_name1 = 'AA' THEN 1 ELSE 2 END, col_name1. Any idea why this doesn't work in my case?
• vandit: I see order by cannot be used in views..