Duplicate query with some non-dupe value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Duplicate query with some non-dupe value

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2009-04-26 : 15:21:45

Hi -
I have a table that has some duplicate rows (based on a Customer ID value), but also has some non-duplicated column values.

For example:


   CustomerID   CustomerType
   1009         Type1
   1009         Type3
   1050         Type1
   2154         Type2
   2154         Type3

I can query the table and those customer id's that have more than one customerType. However, what I am trying to get is a result like:


   CustomerID   CustomerType1   CustomerType2
   1009         Type1           Type3
   2154         Type2           Type3

This is the part I am unsure about how to get to.

Any suggestions would be appreciatd.

Thanks
- will

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-26 : 15:28:53

If you only care about customers with 2 types:

SELECT CustomerID, Min(CustomerType) CustomerType1, Max(CustomerType) CustomerType2
FROM myTable
GROUP BY CustomerID HAVING Count(DISTINCT CustomerType)=2

hanavan
Starting Member

25 Posts

Posted - 2009-04-26 : 18:56:36

Table:
1 type1
1 type2
1 type3
2 type1
2 type2
2 type5
3 type3
4 type1
4 type2

select *
FROM table1
PIVOT
(
MAX(b)
FOR [b] IN ([type1],[type2],[type3],[type4],[type5])
)
AS p

result:

1 type1 type2 type3 NULL NULL
2 type1 type2 NULL NULL type5
3 NULL NULL type3 NULL NULL
4 type1 type2 NULL NULL NULL

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2009-04-27 : 10:48:29

quote:
Originally posted by robvolk

If you only care about customers with 2 types:

SELECT CustomerID, Min(CustomerType) CustomerType1, Max(CustomerType) CustomerType2
FROM myTable
GROUP BY CustomerID HAVING Count(DISTINCT CustomerType)=2

oh no, I care about all types and there can be up to 10.

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2009-04-27 : 10:51:03

quote:
Originally posted by hanavan

select *
FROM table1
PIVOT
(
MAX(b)
FOR [b] IN ([type1],[type2],[type3],[type4],[type5])
)
AS p

result:

1 type1 type2 type3 NULL NULL
2 type1 type2 NULL NULL type5
3 NULL NULL type3 NULL NULL
4 type1 type2 NULL NULL NULL

Thanks for the suggestion. It is close to what I want, I think. However, i need to limit my query to ONLY those customers that have more than one type and then display the customer and all of their types.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources