Sql-server – Select multiple rows from one row based on column values

sql server

I have a product table where the value of two columns can imply either one or two actual product results. If column X = 1 AND Y = 0 return one result, if X = 0 and Y = 1 return one result, if X = 1 AND Y = 1 then return two results.

I want to write a query that will generate two row results for a single row based on the above rule. How can this be done with a SQL query? Is a UNION the only way?

[EDIT based on comment]

TABLE: PRODUCT

ProductId | ABR | UBR
1 | 1 | 1
2 | 1 | 0
3 | 0 | 1
4 | 1 | 1
5 | 1 | 1

I want a SELECT statement that will generate 8 results from this set. Basically one result for each instance of either ABR or UBR = 1.

So I would like my result to be:

ProductId | Edition
1 | ABR
1 | UBR
2 | ABR
3 | UBR
4 | ABR
4 | UBR
5 | ABR
5 | UBR

I know I can achieve this using a UNION but I was looking for something more elegant.

Best Answer

I'd use UNPIVOT for this unless abr and ubr are both indexed and a relatively low proportion contain the value 1.

(Borrowing JNK's table variable)

SELECT id,
       Edition
FROM @prod
UNPIVOT (V FOR Edition IN (abr,ubr)) AS Unpvt
WHERE V = 1

Related Solutions

Sql-server – How to one select a static value as a column based when using UNPIVOT

You are missing a new column generated by unpivoting attributes

SELECT itemNumber, attributes AS [SourceName], Value As [SourceValue]
FROM
(
  SELECT itemNumber, type, code, description
  FROM myTable
) AS tb
UNPIVOT 
(
  Value FOR attributes IN ( type, code, description)
) AS myValues

Sql-server – Turning multiple fields in column to row

You're looking for a pivot. Either of these queries will work: SqlFiddle

/* case */

select
      CustomerId
    , FirstName =max(case when [Key]= 'FirstName' then Value end)
    , LastName  =max(case when [Key]= 'SecondName'  then Value end)
    , Age       =max(case when [Key]= 'Age'       then Value end)
    , Gender    =max(case when [Key]= 'Gender'    then Value end)
from Customers
group by CustomerId 


/* pivot */

select CustomerId, FirstName, SecondName, Age, Gender
  from (Select CustomerId, [Key], Value from Customers) c
    pivot ( max(Value)
      for [Key] in (FirstName, SecondName, Age, Gender)
    ) as p

Schema Setup for SqlFiddle:

    create table Customers (
    id int identity (1,1) not null primary key
  , CustomerID int not null
  , [Key] varchar(32) not null
  , Value varchar(32) 
  )

insert into Customers (CustomerId, [Key], Value) values
    (2,'FirstName','Tim')
  , (2,'SecondName','Skold')
  , (2,'Age','48')
  , (2,'Gender','Male')
  , (3,'FirstName','Sql')
  , (3,'SecondName','Zim')
  , (3,'Age','32')
  , (3,'Gender','Male')

Links

Best Answer

Related Solutions

Sql-server – How to one select a static value as a column based when using UNPIVOT

Sql-server – Turning multiple fields in column to row

Related Question