TSQL in SQL 2005: Query

Question

Hi there

I have 3 tables: Customer, CustomerTypes, CustomerCustomerTypes. CustomerCustomerTypes is basically is a bridge table between the Customer and CustomerTypes.

Table structure: Customers: CustomerID CustomerName

CustomerTypes: CustomerTypeID CusctomerTypeName

CustomerCustomerTypeID CustomerID CustomerTypeID

Sample Data: Customers:

1, ABC
2, CBA

CustomerTypes:

1, Broadcast
2, Banking
3, Retailer

CustomerCustomerTypes:

1, 1
2, 2
2, 3

I want to be able to return query as follow:

ABC; "Broadcasting"
CustomerCustomerTypes; "Banking, Retailer"

as well as to be able to search that string let say "CustomerTypeID = 2"

It will be ruturned as :

CustomerCustomerTypes; "Banking, Retailer"

I can do this with cursor type of query BUT i am just wondering maybe there is a better way.

Thanks

Answer 1

A SQL Server FOR XML query will give you what you want. I haven't had a chance to check the below but it should point you in the right direction.

SELECT c1.CustomerTypeId,
       ( SELECT CustomerTypeName + ',' 
           FROM CustomerTypes c2
          WHERE c2.CustomerTypeId = c1.CustomerTypeIdId
          ORDER BY CustomerTypeName
            FOR XML PATH('') ) AS CustomerTypess
      FROM CustomerCustomerTypes c1
      GROUP BY CustomerTypeId;

Answer 2

Use:

SELECT c.customername,
       STUFF((SELECT ','+ ct.customertypename
               FROM CUSTOMERTYPES ct
               JOIN CUSTOMERCUSTOMERTYPES cct ON cct.customertypeid = ct.customertypeid
              WHERE cct.customerid = C.customerid
           GROUP BY ct.customertypename
            FOR XML PATH('')), 1, 1, '')
 FROM CUSTOMERS c

To be able to search for a specific customer type:

SELECT c.customername,
       STUFF(SELECT ','+ ct.customertypename
               FROM CUSTOMERTYPES ct
               JOIN CUSTOMERCUSTOMERTYPES cct ON cct.customertypeid = ct.customertypeid
              WHERE cct.customerid = C.customerid
           GROUP BY ct.customertypename
            FOR XML PATH(''), 1, 1, '')
 FROM CUSTOMERS c
 JOIN CUSTOMERCUSTOMERTYPES cct ON cct.customertypeid = ct.customertypeid
WHERE cct.customertypeid = @customertypeid