CODEWITHSUNDEEP
sqlsql-server-2014
Kith
Kith

Reputation: 117

How to combine Columns and Rows using a query?

I need to group customers by GroupName wise.Customer can be duplicated on each GroupName. Each GroupName has a unique number called "GroupCode" in table OCQG. Customer table (OCRD) has seperate column for Each GroupCode. As an example, C-0001 customer can have more group names.We can identify GroupCodes for each customer by see Group1,...,Group64 column values.(If this value = Y).Table structure as follows.Please help me.

OCQG Table; enter image description here

OCRD Table;

enter image description here

Upvotes: 0

Views: 85

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270703

If you need to count customers in each group, I would recommend using apply to unpivot and then join:

select ocqg.groupname, count(*)
from ocrd cross apply
     (values (ocrd.group1, 1), (ocrd.group2, 2), . . . 
     ) v(val, groupcode) join
     ocqg
     on v.groupcode = ocgq.groupcode
where v.val = 'Y'
group by ocqg.groupname;

Note that the ocrd table is in a very poor format. You should have a proper junction/association table with one row per customer and group.

Upvotes: 0

HereGoes
HereGoes

Reputation: 1320

The UNPIVOT might help...

SELECT p.customerCode, REPLACE(p.groupcode,'GROUP','') groupcode, ocqg.groupname
FROM ocrd UNPIVOT 
    ( value
     FOR groupcode IN ([Group1],[Group2],[Group3], etc...)
     ) as p,
     ocqg
WHERE value = 'Y' and 
ocqg.groupcode = REPLACE(p.groupcode,'GROUP','')
order by p.customerCode

Upvotes: 1

Related Questions