SQL Query for the Pattern

Question

I tried some queries and its taking me to almost my result using JOINs. But Please suggest if any better way to do this ?

I have table like this :

Name    Number
A       1
B       2
C       3
D       4
E       5

I want the result to be like :

Name    Number  Name    number
A       1       B       2
C       3       D       4
E       5

I have written a query like this :

DECLARE @TempTable1 Table(RowNumber Int, Name varchar(50), Number Int)
DECLARE @TempTable2 Table(RowNumber Int, Name varchar(50), Number Int)

Insert INTO @TempTable1
SELECT ROW_NUMBER() OVER(ORDER BY Name) AS Row, Name, Number
FROM    Test

Insert INTO @TempTable2
SELECT ROW_NUMBER() OVER(ORDER BY Name) AS Row, Name, Number
FROM Test

DECLARE @Count INT 
SELECT  @Count = MAX(RowNumber) FROM @TempTable1

IF ((@Count % 2) = 0)
BEGIN
    SELECT Temp1.RowNumber, Temp1.Name, Temp1.Number, Temp2.RowNumber,Temp2.Name, Temp2.Number
    FROM @TempTable1 Temp1, @TempTable2 Temp2
    WHERE  Temp1.RowNumber = (Temp2.RowNumber - 1) AND (Temp1.RowNumber % 2) != 0  
END

ELSE

BEGIN
    SELECT Temp1.RowNumber, Temp1.Name, Temp1.Number, Temp2.RowNumber,Temp2.Name, Temp2.Number
    FROM @TempTable1 Temp1, @TempTable2 Temp2
    WHERE  Temp1.RowNumber = (Temp2.RowNumber - 1) AND (Temp1.RowNumber % 2) != 0 OR ((Temp1.RowNumber = @Count) AND (Temp2.RowNumber = @Count))
END

can you please explain why you require it in that format and where this result will be used? it sounds like something that is going to be used in a front end application as I can't think how that would be used in SQL. — Tanner
– Tanner, Commented Mar 18, 2015 at 14:20
From where I am calling these values, I need to show in this way, which is not getting possible. — A Developer
– A Developer, Commented Mar 18, 2015 at 14:21

Pragnesh Khalas · Accepted Answer · 2015-03-18 14:25:47Z

1

Below Query will help you

SELECT a.NAME,a.Number,b.NAME,b.Number
FROM
  (SELECT ROW_NUMBER() OVER(ORDER BY Number) AS ROW,NAME,Number  FROM #temp
     WHERE Number %2 <>0) a
LEFT OUTER JOIN
  (SELECT ROW_NUMBER() OVER(ORDER BY Number) AS ROW,NAME,Number  FROM #temp
     WHERE Number %2 =0) b
 ON a.ROW = b.ROW

answered Mar 18, 2015 at 14:25

Pragnesh Khalas

2,8982 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dmitrij Kultasev · Accepted Answer · 2015-03-18 14:27:34Z

0

Using full join as there could be %2 = 1 less than %2 = 0

DECLARE @a AS TABLE (a VARCHAR(10), b INT)
INSERT INTO @a VALUES ('A',1),('B',2),('C',3),('D',4),('E',5);

SELECT a.a,a.b, b.a, b.b 
FROM (
SELECT a.a, a.b, DENSE_RANK() OVER(ORDER BY a.b) AS rownum
FROM @a a
WHERE a.b%2 = 1
) a
FULL JOIN (
SELECT a.a, a.b, DENSE_RANK() OVER(ORDER BY a.b) AS rownum
FROM @a a
WHERE a.b%2 = 0
) b
ON a.rownum = b.rownum

answered Mar 18, 2015 at 14:27

Dmitrij Kultasev

5,8658 gold badges53 silver badges108 bronze badges

Comments

haiiaaa · Accepted Answer · 2015-03-18 14:27:50Z

0

-- Setup
DECLARE @Temp TABLE (Name varchar(50), Number int);

INSERT INTO @Temp
  SELECT 'A', 1 UNION
  SELECT 'B', 2 UNION
  SELECT 'C', 3 UNION
  SELECT 'D', 4 UNION
  SELECT 'E', 5;

-- Query
SELECT
  T1.Name,
  T1.Number,
  T2.Name,
  T2.Number
FROM
  @Temp T1 LEFT JOIN
  @Temp T2 ON T1.Number + 1 = T2.Number
WHERE
  T1.Number % 2 = 1;

answered Mar 18, 2015 at 14:27

haiiaaa

1831 silver badge7 bronze badges

Collectives™ on Stack Overflow

SQL Query for the Pattern

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related