T-SQL: How do I flatten out a table like this?

Question

I have a table I loaded that looks like this:

CUSTID    VALUETYPE    COST
1         A            123
1         B            456
1         C            789
2         B            222

And I need to flatten it out in the same table or insert into a new one to look like this:

CUSTID      A      B      C
1         123    456    789
2           0    222      0

Each row has an identity column not shown.

What would this cursor look like?

Thank you.

What do you mean by `What would this cursor look like?`, do you mean `query`? — SS_DBA, Apr 16 '19 at 16:32
What have you tried??? Have you done any research at all? There tons of example online. — Eric, Apr 16 '19 at 16:47

score 0 · Answer 1 · answered Apr 16 '19 at 16:28

Use a query, such as conditional aggregation:

select custid,
       sum(case when valuetype = 'A' then cost end) as a,
       sum(case when valuetype = 'B' then cost end) as b,
       sum(case when valuetype = 'C' then cost end) as c
from t
group by custid;

score 0 · Answer 2 · answered Apr 16 '19 at 16:29

0

use case when

select custid , sum(case when valuetype='A' then cost else 0 end) A,
           sum(case when valuetype='B' then cost else 0 end) B
,sum(case when valuetype='C' then cost else 0 end) C
from t group by custid

answered Apr 16 '19 at 16:29

Zaynul Abadin Tuhin

31,407
5
33
63

score 0 · Accepted Answer · answered Apr 16 '19 at 16:30

0

I don't see that you need to sum the columns:

select
  custid,
  max(case when valuetype = 'A' then cost else 0 end) A,
  max(case when valuetype = 'B' then cost else 0 end) B,
  max(case when valuetype = 'C' then cost else 0 end) C
from tablename
group by custid

answered Apr 16 '19 at 16:30

forpas

160,666
10
38
76

This one suited my needs the best. Thank you! – Cguy23 Apr 16 '19 at 16:50

score 0 · Answer 4 · answered Apr 16 '19 at 16:36

0

You could make use of a PIVOT

 SELECT 
    CUSTID
    ,ISNULL(p.A,0) AS A
    ,ISNULL(p.B,0) AS B
    ,ISNULL(p.C,0) AS C
FROM t
PIVOT (
    SUM(COST) FOR VALUETYPE IN ([A],[B],[C])) p

answered Apr 16 '19 at 16:36

Will_C

108
5

John Cappelletti · Answer 5 · 2019-04-16T16:53:41.053

If you don't mind NULL values in the Results

 Select *
  From  YourTable
  Pivot (sum(Cost) for ValueType in ([A],[B],[C])) pvt

Returns

CUSTID  A       B       C
1       123     456     789
2       NULL    222     NULL

Otherwise, You Can Eliminate NULL Values

 Select *
  From  (Select * From YourTable
         Union All
         Select A.CustID ,B.VALUETYPE,0
          From  (Select Distinct CustID from YourTable) A
          Cross Join (Select Distinct VALUETYPE from YourTable) B
        ) src
  Pivot (sum(Cost) for ValueType in ([A],[B],[C])) pvt

Returns

CUSTID  A    B      C
1       123  456    789
2       0    222    0

T-SQL: How do I flatten out a table like this?

5 Answers5