CODEWITHSUNDEEP
sqlsql-server
Gopal
Gopal

Reputation: 11982

How to get each 2 rows into single row from table

How to display each 2 rows into single row

Table

Rowid batch maker checker date
1     101   john  null    4feb
2     101   john  raja    4feb
3     101   john  null    5feb
4     101   john  raja    6feb
5     101   john  null    9feb

Expected output

Batch maker checker
101   john  raja    -- 1st and 2nd row
101   john  raja    -- 3rd and 4th row
101   john  null    -- 5th row only

Also table will be n nunber of column

How to make a query to combine each 2 rows into single row

Upvotes: 3

Views: 106

Answers (5)

KumarHarsh
KumarHarsh

Reputation: 5094

try this,

DECLARE @Table TABLE (Rowid int,batch varchar(100),maker varchar(100)
,checker varchar(100),date varchar(100))

INSERT INTO  @Table
SELECT 1,101,'john',null,'4feb' UNION ALL
SELECT 2,101,'john','raja','4feb' UNION ALL
SELECT 3,101,'john',null,'5feb' UNION ALL
SELECT 4,101,'john','raja','6feb' UNION ALL
SELECT 5,101,'john',null,'9feb'
select Batch, maker, checker from @Table
where Rowid  %(2 )=0
union ALL
select Batch, maker, checker from @Table a
where  Rowid  %(2 )=1
and not exists(select * from @Table b
where b.Rowid>a.Rowid and Rowid  %(2 )=0)

Upvotes: 0

udhaya kumar
udhaya kumar

Reputation: 169

Try like this :

DECLARE @Table TABLE (Rowid int,batch varchar(100),maker varchar(100),checker varchar(100),date varchar(100))
DECLARE @Table1 TABLE (batch varchar(100),maker varchar(100),checker varchar(100))
INSERT INTO  @Table
SELECT 1,101,'john',null,'4feb' UNION ALL
SELECT 2,101,'john','raja','4feb' UNION ALL
SELECT 3,101,'john',null,'5feb' UNION ALL
SELECT 4,101,'john','raja','6feb' UNION ALL
SELECT 5,101,'john',null,'9feb'

DECLARE @i INT=1,@RowId INT
WHILE @i<=(SELECT COUNT(*) FROM @Table)
BEGIN
SELECT @RowId= CASE WHEN ISNULL(checker,'0')='0' THEN @i+1 ELSE @i END FROM @Table WHERE Rowid=@i
INSERT INTO @Table1 SELECT batch,maker,checker FROM @Table WHERE Rowid=@RowId
IF @@ROWCOUNT=0
INSERT INTO @Table1 SELECT batch,maker,checker FROM @Table WHERE Rowid=@RowId-1
SET @i=@i+2
END
SELECT * FROM @Table1

Upvotes: 0

Stefano Zanini
Stefano Zanini

Reputation: 5916

You may try with something like this

select  coalesce(t2.batch, t1.batch),
        coalesce(t2.maker, t1.maker),
        coalesce(t2.checker, t1.checker)
from    table t1
left join
        table t2
on      t1.id + 1 = t2.id and
        t1.id % 2 = 1

Odd rows will be joined with the following ones, if they exists. You select columns from the even row if it exists, otherwise from the odd one.

Upvotes: 1

Sebastian Berge
Sebastian Berge

Reputation: 86

SELECT * FROM Players WHERE id % 2 = 0

Upvotes: 0

vikjon0
vikjon0

Reputation: 192

group on col1, isnull(col2,'raja'),col3

Upvotes: 0

Related Questions