none
Split Rows in to another set as columns in sql ?

    Question

  • Hi,

    I have a sample data..

    Id

    ENAME

    GRADE

    1

    MILLER         

    2

    2

    CLARK          

    4

    3

    KING           

    7

    4

    ADAMS          

    5

    5

    FORD           

    1

    6

    JAMES          

    6

    7

    MARTIN         

    10

    8

    ALLEN          

    3

    9

    BLAKE          

    8

    10

    REDDY

    1

    I would like to split 5 rows in to another column set like this..

    Please help me on this.

    Thank you.

    Id

    ENAME

    GRADE

    Id1

    ENAME1

    GRADE1

    1

    MILLER         

    2

    6

    JAMES          

    6

    2

    CLARK          

    4

    7

    MARTIN         

    10

    3

    KING           

    7

    8

    ALLEN          

    3

    4

    ADAMS          

    5

    9

    BLAKE          

    8

    5

    FORD           

    1

    10

    REDDY

    1

    Wednesday, July 09, 2014 3:39 AM

Answers

  • Hi,

    Got a solution for you:)

    CREATE TABLE details(id INT,ename VARCHAR(30),grade VARCHAR(7))
    
    INSERT INTO details VALUES ('1','MILLER','2'),('2','CLARK','4'),('3','KING','7'),('4','ADAMS','5'),('5','FORD','1'),('6','JAMES','6'),('7','MARTIN','10'),('8','ALLEN','3'),('9','BLAKE','8'),('10','REDDY','1')
    
    WITH cte 
    AS
    (
    SELECT Id,ename,grade
        ,NTILE(2) OVER(ORDER BY id ASC) AS cnt
    FROM details 
    )
    SELECT ROW_NUMBER() OVER (ORDER BY Id) as rnum,id,ename,grade INTO #t1 FROM cte WHERE cnt=1
    
    WITH cte 
    AS
    (
    SELECT Id,ename,grade
        ,NTILE(2) OVER(ORDER BY id ASC) AS cnt
    FROM details 
    )
    SELECT ROW_NUMBER() OVER (ORDER BY Id) as rnum,id AS id1,ename AS ename1,grade AS grade1 INTO #t2 FROM cte WHERE cnt=2
    
    SELECT a.id,a.ename,a.grade,b.id1,b.ename1,b.ename1 FROM #t1 a,#t2 b WHERE a.rnum=b.rnum

    Please mark it as answer if it really helped you..:)

    • Proposed as answer by Suresh Sekar Wednesday, July 09, 2014 11:09 AM
    • Marked as answer by Narsa Thursday, July 10, 2014 2:44 AM
    Wednesday, July 09, 2014 10:29 AM
  • You may be much better doing this at front end

    But if you want to implement it in sql server this is the way to go

    SELECT MAX(CASE WHEN GrpNo = 0  THEN Id END) AS Id,
    MAX(CASE WHEN GrpNo = 0  THEN Ename END) AS ENAME,
    MAX(CASE WHEN GrpNo = 0  THEN Grade END) AS Grade,
    MAX(CASE WHEN GrpNo = 1 THEN Id END) AS Id1,
    MAX(CASE WHEN GrpNo = 1 THEN Ename END) AS ENAME1,
    MAX(CASE WHEN GrpNo = 1  THEN Grade END) AS Grade1,
    MAX(CASE WHEN GrpNo = 2 THEN Id END) AS Id2,
    MAX(CASE WHEN GrpNo = 2 THEN Ename END) AS ENAME2,
    MAX(CASE WHEN GrpNo = 2  THEN Grade END) AS Grade2,
    ...
    FROM
    (
    SELECT *,
    (ROW_NUMBER() OVER (ORDER BY Id)-1)/5 AS GrpNo,
    (ROW_NUMBER() OVER (ORDER BY Id)-1) % 5 AS Seq
    FROM Table
    )t
    GROUP BY Seq
    ORDER BY Seq

    to make it dynamic see

    http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Uri DimantMVP, Editor Wednesday, July 09, 2014 11:08 AM
    • Marked as answer by Narsa Thursday, July 10, 2014 2:44 AM
    Wednesday, July 09, 2014 4:32 AM

All replies

  • This you should do at your presentation level, Reporting services may be....

    Satheesh
    My Blog | How to ask questions in technical forum


    Wednesday, July 09, 2014 4:14 AM
  • You may be much better doing this at front end

    But if you want to implement it in sql server this is the way to go

    SELECT MAX(CASE WHEN GrpNo = 0  THEN Id END) AS Id,
    MAX(CASE WHEN GrpNo = 0  THEN Ename END) AS ENAME,
    MAX(CASE WHEN GrpNo = 0  THEN Grade END) AS Grade,
    MAX(CASE WHEN GrpNo = 1 THEN Id END) AS Id1,
    MAX(CASE WHEN GrpNo = 1 THEN Ename END) AS ENAME1,
    MAX(CASE WHEN GrpNo = 1  THEN Grade END) AS Grade1,
    MAX(CASE WHEN GrpNo = 2 THEN Id END) AS Id2,
    MAX(CASE WHEN GrpNo = 2 THEN Ename END) AS ENAME2,
    MAX(CASE WHEN GrpNo = 2  THEN Grade END) AS Grade2,
    ...
    FROM
    (
    SELECT *,
    (ROW_NUMBER() OVER (ORDER BY Id)-1)/5 AS GrpNo,
    (ROW_NUMBER() OVER (ORDER BY Id)-1) % 5 AS Seq
    FROM Table
    )t
    GROUP BY Seq
    ORDER BY Seq

    to make it dynamic see

    http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Uri DimantMVP, Editor Wednesday, July 09, 2014 11:08 AM
    • Marked as answer by Narsa Thursday, July 10, 2014 2:44 AM
    Wednesday, July 09, 2014 4:32 AM
  • Hi Narsa,

    Try this:

    select  tblupto5.Id
           ,tblupto5.ENAME
           ,tblupto5.GRADE
           ,tblgrterthen5.Id as Id1
           ,tblgrterthen5.ENAME as ENAME1
           ,tblgrterthen5.GRADE as GRADE1
    from dbo.<<table>> as tblupto5
    inner join dbo.<<table>> as tblgrterthen5 on tblupto5.Id = (tblgrterthen5.Id + 5)


    Regards Harsh

    Wednesday, July 09, 2014 4:57 AM
  • Hi,

    Could you please check this out

    SELECT ROW_NUMBER() OVER (PARTITION BY ORD ORDER BY ID) ROWNUM , ORD, ID, NAME, GRADE
    FROM
    (
    	SELECT NTILE(2) OVER(ORDER BY ID) ORD, ID, NAME, GRADE
    	FROM dbo.splittest
    )D

    The above query will rank your data and divide into two parts (1, 2) - ORD column- you can then perform SELF JOIN go get your result.

    I hope this is helpful.


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid

    MCSE Data Platform
    MCITP: SQL Server 2008 Administration/Development
    MCSA SQL Server 2012
    MCTS: SQL Server Administration/Development

    MyBlog

    Wednesday, July 09, 2014 6:48 AM
  • Hi,

    Got a solution for you:)

    CREATE TABLE details(id INT,ename VARCHAR(30),grade VARCHAR(7))
    
    INSERT INTO details VALUES ('1','MILLER','2'),('2','CLARK','4'),('3','KING','7'),('4','ADAMS','5'),('5','FORD','1'),('6','JAMES','6'),('7','MARTIN','10'),('8','ALLEN','3'),('9','BLAKE','8'),('10','REDDY','1')
    
    WITH cte 
    AS
    (
    SELECT Id,ename,grade
        ,NTILE(2) OVER(ORDER BY id ASC) AS cnt
    FROM details 
    )
    SELECT ROW_NUMBER() OVER (ORDER BY Id) as rnum,id,ename,grade INTO #t1 FROM cte WHERE cnt=1
    
    WITH cte 
    AS
    (
    SELECT Id,ename,grade
        ,NTILE(2) OVER(ORDER BY id ASC) AS cnt
    FROM details 
    )
    SELECT ROW_NUMBER() OVER (ORDER BY Id) as rnum,id AS id1,ename AS ename1,grade AS grade1 INTO #t2 FROM cte WHERE cnt=2
    
    SELECT a.id,a.ename,a.grade,b.id1,b.ename1,b.ename1 FROM #t1 a,#t2 b WHERE a.rnum=b.rnum

    Please mark it as answer if it really helped you..:)

    • Proposed as answer by Suresh Sekar Wednesday, July 09, 2014 11:09 AM
    • Marked as answer by Narsa Thursday, July 10, 2014 2:44 AM
    Wednesday, July 09, 2014 10:29 AM
  • Is the matching column always going to be 5 more than the starting column?

    The posted solutions so far rely on this, as there is no other relationship from the information you have given us.

    Wednesday, July 09, 2014 1:53 PM