Displaying rows in multiple columns

Not sure if this is doable using sql alone or not, but here is the problem. I have a weird requirement that data needs to be displayed in columns so users can compare data quickly!

Here is what the result set looks like right now

CustomerID  Company     Active
001         ATT        Y
002         ATT        N
003         ATT        Y
001         VZ         Y
002         VZ         N
003         VZ         Y
001         TM         Y
002         TM         Y
003         TM         Y

Now this is how they want to see it

CustomerID  Company     Active      Company     Active      Company     Active
001        ATT         Y           VZ          Y            TM         Y
002        ATT     开发者_StackOverflow    N           VZ          N            TM         Y
003        ATT         Y           VZ          Y            TM         Y

Assumptions:

• This could be a pretty long table, that's why they want to see all companies on one row, rather than needing to scroll down to see if active or not.
  • Nummber of companies is between 1-3 in most cases

Any help is appreciated.

Thanks!

A version of the query might look like this:

SELECT
   CustList.CustomerId
  ,t1.Company
  ,t1.Active
  ,t2.Company
  ,t2.Active
  ,t3.Company
  ,t3.Active
 from (select distinct CustomerId from MyTable) CustList
  left outer join MyTable t1  --  outer join, in case a customer might not "have" a given company
   on t1.CustomerId = CustList.CustomerId
  left outer join MyTable t2
   on t2.CustomerId = CustList.CustomerId
  left outer join MyTable t3
   on t3.CustomerId = CustList.CustomerId
 where t1.Company = 'Att'
  and t2.Company = 'VZ'
  and t3.Company = 'TM'

However, unless you know ahead of time what the company names are and how many there are, you'll have to build the query and run it dynamically each time, by:

• First identify the list of companies to be included
• Loop through that list and add in statements (columns, join, where/and) for each company (t1, t2, etc.)
• Dynamically execute the query

Fussy, but doable.

-- EDIT ---------------------------------

Nuts. I still have trouble with outer joins. (I'd like to think if I wrote this against real tables I'd get it right...)

I believe the query is "linking in" too many rows with each outer join after the first. Try this:

SELECT 
   CustList.CustomerId 
  ,t1.Company 
  ,t1.Active 
  ,t2.Company 
  ,t2.Active 
  ,t3.Company 
  ,t3.Active 
 from (select distinct CustomerId from MyTable) CustList 
  left outer join MyTable t1  --  outer join, in case a customer might not "have" a given company 
   on t1.CustomerId = CustList.CustomerId 
    and t1.Company = 'ATT'
  left outer join MyTable t2 
   on t2.CustomerId = CustList.CustomerId 
    and t2.Company = 'VZ'
  left outer join MyTable t3 
   on t3.CustomerId = CustList.CustomerId 
    and t3.Company = 'TM'

When testing or working out bugs on this, try running it for one company, and then add companies one by one to see what happens.

If the list of companies is always limited to exactly the same collection of values, which you suggest in comments is true, you can use the PIVOT feature (SQL Server 2005 and up):

SELECT CustomerID, [ATT], [VZ], [TM]
FROM (/* whatever gave you that result set above */) AS NormalizedData
PIVOT (MAX(Active) FOR Company IN ([ATT],[VZ],[TM])) AS PivotedData

The MAX() is only there since you have to use an aggregate function with PIVOT; if there's only ever one value for a company, that shouldn't be a problem.

This doesn't give exactly the same result set as the one you asked for. If the specified output column list is a requirement, change the select clause to:

SELECT
    CustomerID,
    -- Use CASE so output is NULL if Active column is NULL
    -- If you don't care, use: 'ATT' AS Company
    CASE WHEN [ATT] IS NOT NULL THEN 'ATT' END AS Company,
    [ATT] AS Active,
    CASE WHEN [VZ] IS NOT NULL THEN 'VZ' END AS Company,
    [VZ] AS Active,
    CASE WHEN [TM] IS NOT NULL THEN 'TM' END AS Company,
    [TM] AS Active

But, I think they will like the condensed output better since it makes it easier to compare all the values.

If there is no row for a specific company, it will return a NULL in that column. Sadly, there is no way I know of to make the PIVOT column list dynamic (as of SQL Server 2005 -- haven't looked at 2008) short of using dynamic sql. So, if another company gets added (say, 'SPR') you will have to go back and change the query.

Another thing you might want to do to help them compare is to check for duplicates directly by adding:

WHERE CustomerID IN
    (SELECT CustomerID FROM (/* original query */) Data
    GROUP BY CustomerID, Active HAVING COUNT(*) > 1)

This will restrict the output list to only those items with multiple Active flags. This can be added to either your original query or the pivoted one, although with the pivoted one you can do comparisons with the output columns directly and avoid the additional subquery. No sense in manually scanning down a list of values when you can make the computer do it for you.

Your best bet is to look at doing this via dynamic SQL.