CODEWITHSUNDEEP
sqlhana
SQL3026
SQL3026

Reputation: 29

Rows Columns Traverse

I have data in the below format

id      idnew
 1       2
 3       4
 2       
 4       7
 6       8
 7

Result Should be something like this

ID should be followed by idnew 

 1
 2
 3
 4
 2
 4
 7
 6
 8
 7       


Thanks in advance

Upvotes: 0

Views: 177

Answers (3)

Sreenu131
Sreenu131

Reputation: 2516

Using Cross Apply

;WITH CTE (id,idnew)
AS
(
SELECT 1,2      UNION ALL
SELECT 3,4      UNION ALL
SELECT 2,NULL   UNION ALL
SELECT 4,7      UNION ALL
SELECT 6,8      UNION ALL
SELECT 7,NULL
)
SELECT New 
FROM CTE
CROSS APPLY ( VALUES (id),(idnew))AS Dt (New)
WHERE dt.New IS NOT NULL

Result

New
---
1
2
3
4
2
4
7
6
8
7

Upvotes: 0

Crogacht
Crogacht

Reputation: 90

This should maintain the order:

 SELECT id
   FROM (
        SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS RowNumber
          FROM myTable
         UNION ALL
        SELECT idnew, ROW_NUMBER() OVER (ORDER BY idnew) + 
               (SELECT COUNT(*) FROM dbo.myTable) AS RowNumber
          FROM myTable
         WHERE idnew IS NOT NULL
        ) a
  ORDER BY RowNumber

I am assuming the id column is NOT NULL-able.

NOTE: If you want to keep the NULL values from the idnew column AND maintain the order, then remove the WHERE clause and ORDER BY id in the second select:

 SELECT id
   FROM (
        SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS RowNumber
          FROM myTable
         UNION ALL
        SELECT idnew, ROW_NUMBER() OVER (ORDER BY id) + 
               (SELECT COUNT(*) FROM dbo.myTable) AS RowNumber
          FROM myTable
        ) a
  ORDER BY RowNumber

Upvotes: 2

Indominus
Indominus

Reputation: 1248

This is fully tested, try it here: https://rextester.com/DVZXO21058

Setting up the table as you described:

   CREATE TABLE myTable (id INT, idnew INT);

   INSERT INTO myTable (id, idnew)
   VALUES (1, 2),
          (3, 4),
          (2, NULL),
          (4, 7),
          (6, 8),
          (7, NULL);

   SELECT * FROM myTable;

Here is the query to do the trick:

   SELECT mixed_id FROM
   (
       SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_num,
              id,
              idnew
       FROM myTable
   ) AS x
   UNPIVOT
   (
       mixed_id for item in (id, idnew)
   ) AS y
   WHERE mixed_id IS NOT NULL
   ORDER BY row_num, mixed_id;

In order not to further complicate the query, this is taking advantage of 'id' would rank ahead of 'idnew' as a string. I believe string ranking is not the key issue here.

Upvotes: 1

Related Questions