Sql-server – Create a row based on a condition (column value)

querysql server

I have a table that contains the following data;

EmpNo | GivenName | PreferredName 
---------------------------------
1     | Paris     | Paris
2     | Ashley    | Ash

I am trying to get an output where, if the GivenName and PreferredName values are same, then the row should be returned as it is, but if they are different then the row should be returned twice (if possible, the row returned twice should have a NULL in PreferredName in the first occurence and GivenName a NULL in the second). Something like this:

EmpNo | GivenName | PreferredName 
---------------------------------
1     | Paris     | Paris
2     | Ashley    | NULL
2     | NULL      | Ash

I have no idea where to begin. I tried using CASE statement and FULL OUTER JOIN to no avail. I have as well tried using the code below, and it is giving me the answer for the first part of my question:

SELECT [EmpNo]
      ,[GivenName]
      ,[PreferredName]
FROM [Emp] E
WHERE [GivenName]= [PreferredName]
UNION ALL
SELECT [EmpNo]
      ,[GivenName]
      ,[PreferredName]
FROM [Emp]

Best Answer

You can do this with the below query:

DECLARE @Employees TABLE
(
    EmpNo INT,
    GivenName NVARCHAR(255),
    PreferredName NVARCHAR(255)
)

INSERT INTO  @Employees
VALUES (1, 'Paris', 'Paris'), (2, 'Ashley', 'Ash')

SELECT EmpNo,
    GivenName,
    PreferredName
FROM @Employees
WHERE GivenName = PreferredName
UNION ALL
SELECT EmpNo,
    GivenName,
    NULL AS PreferredName
FROM @Employees
WHERE GivenName <> PreferredName
UNION ALL
SELECT EmpNo,
    NULL AS GivenName,
    PreferredName
FROM @Employees
WHERE GivenName <> PreferredName

Related Solutions

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

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

Sql-server – How to get the first or last row in ordered result set, depending by column value

Doing that is tricky, because you need to check the differences between the data in both orders, ascending to get the first 'IN', descending to get the last 'OUT'.

With I AS (
  SELECT EMPLID, Name, PUNCHDATE, PUNCHTIME, DEVICETYPE
       , ID_Up = Row_Number() OVER (PARTITION BY EMPLID 
                                    ORDER BY EMPLID, PUNCHDATE, PUNCHTIME)
       , ID_Down = Row_Number() OVER (PARTITION BY EMPLID 
                                      ORDER BY EMPLID, PUNCHDATE DESC, PUNCHTIME DESC)
  FROM   Table1
), S AS (
  SELECT a.EMPLID, a.Name, a.PUNCHDATE, a.PUNCHTIME, a.DEVICETYPE
       , a.ID_Up aUID, b.ID_Up bUID, a.ID_Down aDID, c.ID_Down cDID
       , a.DeviceType aDeviceType, b.DeviceType bDeviceType, c.DeviceType cDeviceType
       , OKValues 
       = CASE WHEN a.ID_Up = 1 THEN 1
              WHEN a.DeviceType = 'IN' AND b.DeviceType = 'OUT' THEN 1
              WHEN a.ID_Down = 1 THEN 1
              WHEN a.DeviceType = 'OUT' AND c.DeviceType = 'IN' THEN 1
              ELSE 0
         END
  FROM   I a
         LEFT  JOIN I b ON a.EmplID = b.EmplID And b.ID_Up = a.ID_Up - 1
         LEFT  JOIN I c ON a.EmplID = c.EmplID And c.ID_Down = a.ID_Down - 1
)
SELECT EMPLID, Name, PUNCHDATE, PUNCHTIME, DEVICETYPE
FROM   S
WHERE  OKValues = 1
ORDER BY EMPLID, PUNCHDATE, PUNCHTIME

SQLFiddle

the S CTE is to remove the CASE from the WHERE condition, in the CTE I left a lot of noise data, that can be useful to understand the CASE conditions

Best Answer

Related Solutions

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

Sql-server – How to get the first or last row in ordered result set, depending by column value

Related Question