How can I group values from different columns into one row?
2 answers
I think the easiest way to do this is to apply a function UNPIVOT
and PIVOT
to get the result:
select account, description,
[value_1], [value_2], [value_3], [value_4]
from
(
select account, description, col, value,
row_number() over(partition by account, col order by col) rn
from
(
select [account], [description], [value_1], [value_2], [value_3], [value_4]
from yourtable
) src
unpivot
(
value
for col in ([value_1], [value_2], [value_3], [value_4])
) un
) s
pivot
(
max(value)
for col in ([value_1], [value_2], [value_3], [value_4])
) piv
See SQL Fiddle with Demo .
This can also be done by using UNION ALL
both the univot and aggregate function and the expression CASE
:
select account, description,
max(case when col = 'value_1' then value end) value_1,
max(case when col = 'value_2' then value end) value_2,
max(case when col = 'value_3' then value end) value_3,
max(case when col = 'value_4' then value end) value_4
from
(
select account, description, col, value,
row_number() over(partition by account, col order by account) rn
from
(
select [account], [description], 'value_1' col, [value_1] value
from yourtable
where [value_1] is not null
union all
select [account], [description], 'value_2' col, [value_2] value
from yourtable
where [value_2] is not null
union all
select [account], [description], 'value_3' col, [value_3] value
from yourtable
where [value_3] is not null
union all
select [account], [description], 'value_4' col, [value_4] value
from yourtable
where [value_4] is not null
) s
) un
group by account, description, rn
Both give the result:
| ACCOUNT | DESCRIPTION | VALUE_1 | VALUE_2 | VALUE_3 | VALUE_4 |
----------------------------------------------------------------------
| A00005 | Account Desc | ABCD0081 | BCDE0010 | BKCP0010 | SMTP0010 |
| A00005 | Account Desc | ABCD0082 | (null) | BKCP0011 | (null) |
+10
source to share
One idea would be to use it Row_Number()
as a union field. Something like that:
SELECT T.Account, T.Description, T1.Value_1, T2.Value_2, T3.Value_3, T4.Value_4
From
(
SELECT Account, Description, Value_1, Value_2, Value_3, Value_4,
Row_number() OVER (ORDER BY (SELECT 0)) rn
FROM YourTable
) T
LEFT JOIN (
SELECT Account, Value_1, Row_number() OVER (ORDER BY (SELECT 0)) rn
FROM YourTable
WHERE Value_1 <> '') T1 ON T.Account = T1.Account AND T.rn = T1.rn
LEFT JOIN (
SELECT Account, Value_2, Row_number() OVER (ORDER BY (SELECT 0)) rn
FROM YourTable
WHERE Value_2 <> ''
) T2 ON T.Account = T2.Account AND T.rn = T2.rn
LEFT JOIN (
SELECT Account, Value_3, Row_number() OVER (ORDER BY (SELECT 0)) rn
FROM YourTable
WHERE Value_3 <> ''
) T3 ON T.Account = T3.Account AND T.rn = T3.rn
LEFT JOIN (
SELECT Account, Value_4, Row_number() OVER (ORDER BY (SELECT 0)) rn
FROM YourTable
WHERE Value_4 <> ''
) T4 ON T.Account = T4.Account AND T.rn = T4.rn
WHERE T1.Value_1 IS NOT NULL
OR T2.VALUE_2 IS NOT NULL
OR T3.VALUE_3 IS NOT NULL
OR T4.VALUE_4 IS NOT NULL
If your values are stored as spaces, then this should work. If they are stored as NULL, replace <> '' with IS NOT NULL.
Here is the SQL Fiddle .
Good luck.
0
source to share