using case operator to group records

[arodri]

Hello,

I played with this data for an entire day and still couldn't figure out how to get it the way I need it. Here is a sample of my data:

Site CustID Name PhoneNumber Code
TX01 101 Jane Smith 222-222-222 A1C357
TX01 103 John Key NULL A1C258
TX01 103 John Key NULL B999-tx
TX01 104 Jane Thomas NULL A1C987
Tx01 104 JAne Thomas NULL C78d-tx

-The CustID is unique for every customer
-There are 3 types of Codes
1. The ones that start with "A1C"
2. The ones that end with "-tx"
3. The ones that have a "+" in the code
-The same customer can have a record for one, two , or three of these codes (ex. John Key & Jane Thomas in sampe data above have A1C and -tx codes)


What I need to do with this data is somehow group it (I have also tried a CASE and UNION statements) so that the data displays only ONE record per person and lists all of their codes in that record, like this:

Site CustID Name PhoneNumber Code-A1C Code-tx Code+
TX01 101 Jane Smith 222-222-222 A1C357 NULL NULL
TX01 103 John Key NULL A1C258 B999-tx NULL
TX01 104 Jane Thomas NULL A1C987 C78d-tx NULL


Any ideas on how to accomplish this?? I REALLY need to figure something out and I ran out of ideas.

Thank you!

 

[markros]

Try

;with cte as (select *, row_number() over (partition by CustID
order by case when left(Code,3) = 'A1C' then 1 when right(Code,3) = '-tx' then 2 else 3 end) as Row from myTable)

select Site, CustID,    Name,          PhoneNumber,
[1] as [Code-A1C], [2] as [Code-tx], [3] as [Code+]
from cte PIVOT (max(Code) for Row in ([1],[2],[3])) pvt

PluralSight Learning Library

 

[arodri]

Thanks so much for the suggestion! I actually got it to work with just a CASE operator, but I played around with grouping and aggregating the rest of the columns.

I'm working on something right now where I need to take the difference of the fist and last record of each CardNum(the column I group by). I posted another question about this but I think I may need to use "with CTE" and "over partition by." If it's not too much trouble could you biefly explain what those do?

Thanks!

 

[markros]

CTE is a common table expression and it's a very nice addition in the language. In most cases you use CTE instead of derived tables. You can write the query above using subquery (derived table), but then it will be a bit more harder to understand. So, I use them primarily for maintenance and readability purposes.

ROW_NUMBER() and other ranking window functions is another important and great addition to T-SQL and it allows to solve the problems of getting aggregated values with ease.

BTW, in regards to aggregated values you may find these two blogs an interesting reading (especially the first one as the second is just use cases):
Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2



PluralSight Learning Library