PIVOT and insert every N rows










2















There are two tables:



First table has two columns with the following data:



[col1] [RowIndex]
apple 1
10 2
red 3
pear 4
20 5
yellow 6


Second empty table has 3 columns:



[FruitType] [Weight] [Color] 


I wish to load the first table's [col1] data into the second one but "rotated" and without any aggregation.



Result would would look like this:



[FruitType] [Weight] [Color] 
apple 10 red
pear 20 yellow


How can i insert data to the second table? I tried PIVOT but cannot wrap my head around.










share|improve this question



















  • 4





    How could you know which row match which colunm? is there any order in you first table?

    – D-Shih
    Nov 12 '18 at 15:59











  • First you are going to have to normalize that column... don't worry about pivot yet. Define how you associate red to apply and 1 to apple

    – scsimon
    Nov 12 '18 at 16:00











  • You can't, unless there is a way to associate the different records.

    – Zohar Peled
    Nov 12 '18 at 16:03











  • @D-Shih I have edited the question. I have a rowindex (provided by application) in the first table. So the order is always strictly according to that column.

    – Avithohol
    Nov 12 '18 at 16:09















2















There are two tables:



First table has two columns with the following data:



[col1] [RowIndex]
apple 1
10 2
red 3
pear 4
20 5
yellow 6


Second empty table has 3 columns:



[FruitType] [Weight] [Color] 


I wish to load the first table's [col1] data into the second one but "rotated" and without any aggregation.



Result would would look like this:



[FruitType] [Weight] [Color] 
apple 10 red
pear 20 yellow


How can i insert data to the second table? I tried PIVOT but cannot wrap my head around.










share|improve this question



















  • 4





    How could you know which row match which colunm? is there any order in you first table?

    – D-Shih
    Nov 12 '18 at 15:59











  • First you are going to have to normalize that column... don't worry about pivot yet. Define how you associate red to apply and 1 to apple

    – scsimon
    Nov 12 '18 at 16:00











  • You can't, unless there is a way to associate the different records.

    – Zohar Peled
    Nov 12 '18 at 16:03











  • @D-Shih I have edited the question. I have a rowindex (provided by application) in the first table. So the order is always strictly according to that column.

    – Avithohol
    Nov 12 '18 at 16:09













2












2








2








There are two tables:



First table has two columns with the following data:



[col1] [RowIndex]
apple 1
10 2
red 3
pear 4
20 5
yellow 6


Second empty table has 3 columns:



[FruitType] [Weight] [Color] 


I wish to load the first table's [col1] data into the second one but "rotated" and without any aggregation.



Result would would look like this:



[FruitType] [Weight] [Color] 
apple 10 red
pear 20 yellow


How can i insert data to the second table? I tried PIVOT but cannot wrap my head around.










share|improve this question
















There are two tables:



First table has two columns with the following data:



[col1] [RowIndex]
apple 1
10 2
red 3
pear 4
20 5
yellow 6


Second empty table has 3 columns:



[FruitType] [Weight] [Color] 


I wish to load the first table's [col1] data into the second one but "rotated" and without any aggregation.



Result would would look like this:



[FruitType] [Weight] [Color] 
apple 10 red
pear 20 yellow


How can i insert data to the second table? I tried PIVOT but cannot wrap my head around.







sql sql-server tsql sql-server-2014






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 12 '18 at 16:07







Avithohol

















asked Nov 12 '18 at 15:58









AvithoholAvithohol

435718




435718







  • 4





    How could you know which row match which colunm? is there any order in you first table?

    – D-Shih
    Nov 12 '18 at 15:59











  • First you are going to have to normalize that column... don't worry about pivot yet. Define how you associate red to apply and 1 to apple

    – scsimon
    Nov 12 '18 at 16:00











  • You can't, unless there is a way to associate the different records.

    – Zohar Peled
    Nov 12 '18 at 16:03











  • @D-Shih I have edited the question. I have a rowindex (provided by application) in the first table. So the order is always strictly according to that column.

    – Avithohol
    Nov 12 '18 at 16:09












  • 4





    How could you know which row match which colunm? is there any order in you first table?

    – D-Shih
    Nov 12 '18 at 15:59











  • First you are going to have to normalize that column... don't worry about pivot yet. Define how you associate red to apply and 1 to apple

    – scsimon
    Nov 12 '18 at 16:00











  • You can't, unless there is a way to associate the different records.

    – Zohar Peled
    Nov 12 '18 at 16:03











  • @D-Shih I have edited the question. I have a rowindex (provided by application) in the first table. So the order is always strictly according to that column.

    – Avithohol
    Nov 12 '18 at 16:09







4




4





How could you know which row match which colunm? is there any order in you first table?

– D-Shih
Nov 12 '18 at 15:59





How could you know which row match which colunm? is there any order in you first table?

– D-Shih
Nov 12 '18 at 15:59













First you are going to have to normalize that column... don't worry about pivot yet. Define how you associate red to apply and 1 to apple

– scsimon
Nov 12 '18 at 16:00





First you are going to have to normalize that column... don't worry about pivot yet. Define how you associate red to apply and 1 to apple

– scsimon
Nov 12 '18 at 16:00













You can't, unless there is a way to associate the different records.

– Zohar Peled
Nov 12 '18 at 16:03





You can't, unless there is a way to associate the different records.

– Zohar Peled
Nov 12 '18 at 16:03













@D-Shih I have edited the question. I have a rowindex (provided by application) in the first table. So the order is always strictly according to that column.

– Avithohol
Nov 12 '18 at 16:09





@D-Shih I have edited the question. I have a rowindex (provided by application) in the first table. So the order is always strictly according to that column.

– Avithohol
Nov 12 '18 at 16:09












2 Answers
2






active

oldest

votes


















2














A conditional aggregation in concert with row_number() should do the trick



Example



Declare @YourTable Table ([RowIndex] int,[col1] varchar(50))
Insert Into @YourTable Values
(1,'apple')
,(2,'1')
,(3,'red')
,(4,'pear')
,(5,'2')
,(6,'yellow')

Select [FruitType] = max(case when col=1 then Col1 end)
,[Weight] = max(case when col=2 then Col1 end)
,[Color] = max(case when col=0 then Col1 end)
From (
Select *
,Grp = (Row_Number() over (order by [RowIndex]) - 1) / 3
,Col = Row_Number() over (order by [RowIndex]) % 3
From @YourTable
) A
Group By Grp


Returns



FruitType Weight Color
apple 1 red
pear 2 yellow


NOTE:



If RowIndex is truly sequential, you can remove the row_number() function and simply use RowIndex






share|improve this answer























  • Thanks. Yes the RowIndex is truly sequential.

    – Avithohol
    Nov 12 '18 at 16:36






  • 1





    @Avithohol Great! Then you can replace Row_Number() over (order by [RowIndex]) with [RowIndex]

    – John Cappelletti
    Nov 12 '18 at 16:38


















2














You can try to use ROW_NUMBER window function with some calculation



insert into SecondTable ( FruitType,Weight,Color)
SELECT FruitType,Weight,Color
FROM (
SELECT MAX(CASE WHEN rn % 3 = 1 THEN [col1] END) FruitType,
MAX(CASE WHEN rn % 3 = 2 THEN [col1] END) Weight,
MAX(CASE WHEN rn % 3 = 0 THEN [col1] END) Color
FROM (
SELECT *,ROW_NUMBER() OVER(ORDER BY RowIndex) rn
FROM FirstTable
) tt
GROUP BY (rn - 1) /3
)tt


sqlfiddle






share|improve this answer
























    Your Answer






    StackExchange.ifUsing("editor", function ()
    StackExchange.using("externalEditor", function ()
    StackExchange.using("snippets", function ()
    StackExchange.snippets.init();
    );
    );
    , "code-snippets");

    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "1"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader:
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    ,
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );













    draft saved

    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53265799%2fpivot-and-insert-every-n-rows%23new-answer', 'question_page');

    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    2














    A conditional aggregation in concert with row_number() should do the trick



    Example



    Declare @YourTable Table ([RowIndex] int,[col1] varchar(50))
    Insert Into @YourTable Values
    (1,'apple')
    ,(2,'1')
    ,(3,'red')
    ,(4,'pear')
    ,(5,'2')
    ,(6,'yellow')

    Select [FruitType] = max(case when col=1 then Col1 end)
    ,[Weight] = max(case when col=2 then Col1 end)
    ,[Color] = max(case when col=0 then Col1 end)
    From (
    Select *
    ,Grp = (Row_Number() over (order by [RowIndex]) - 1) / 3
    ,Col = Row_Number() over (order by [RowIndex]) % 3
    From @YourTable
    ) A
    Group By Grp


    Returns



    FruitType Weight Color
    apple 1 red
    pear 2 yellow


    NOTE:



    If RowIndex is truly sequential, you can remove the row_number() function and simply use RowIndex






    share|improve this answer























    • Thanks. Yes the RowIndex is truly sequential.

      – Avithohol
      Nov 12 '18 at 16:36






    • 1





      @Avithohol Great! Then you can replace Row_Number() over (order by [RowIndex]) with [RowIndex]

      – John Cappelletti
      Nov 12 '18 at 16:38















    2














    A conditional aggregation in concert with row_number() should do the trick



    Example



    Declare @YourTable Table ([RowIndex] int,[col1] varchar(50))
    Insert Into @YourTable Values
    (1,'apple')
    ,(2,'1')
    ,(3,'red')
    ,(4,'pear')
    ,(5,'2')
    ,(6,'yellow')

    Select [FruitType] = max(case when col=1 then Col1 end)
    ,[Weight] = max(case when col=2 then Col1 end)
    ,[Color] = max(case when col=0 then Col1 end)
    From (
    Select *
    ,Grp = (Row_Number() over (order by [RowIndex]) - 1) / 3
    ,Col = Row_Number() over (order by [RowIndex]) % 3
    From @YourTable
    ) A
    Group By Grp


    Returns



    FruitType Weight Color
    apple 1 red
    pear 2 yellow


    NOTE:



    If RowIndex is truly sequential, you can remove the row_number() function and simply use RowIndex






    share|improve this answer























    • Thanks. Yes the RowIndex is truly sequential.

      – Avithohol
      Nov 12 '18 at 16:36






    • 1





      @Avithohol Great! Then you can replace Row_Number() over (order by [RowIndex]) with [RowIndex]

      – John Cappelletti
      Nov 12 '18 at 16:38













    2












    2








    2







    A conditional aggregation in concert with row_number() should do the trick



    Example



    Declare @YourTable Table ([RowIndex] int,[col1] varchar(50))
    Insert Into @YourTable Values
    (1,'apple')
    ,(2,'1')
    ,(3,'red')
    ,(4,'pear')
    ,(5,'2')
    ,(6,'yellow')

    Select [FruitType] = max(case when col=1 then Col1 end)
    ,[Weight] = max(case when col=2 then Col1 end)
    ,[Color] = max(case when col=0 then Col1 end)
    From (
    Select *
    ,Grp = (Row_Number() over (order by [RowIndex]) - 1) / 3
    ,Col = Row_Number() over (order by [RowIndex]) % 3
    From @YourTable
    ) A
    Group By Grp


    Returns



    FruitType Weight Color
    apple 1 red
    pear 2 yellow


    NOTE:



    If RowIndex is truly sequential, you can remove the row_number() function and simply use RowIndex






    share|improve this answer













    A conditional aggregation in concert with row_number() should do the trick



    Example



    Declare @YourTable Table ([RowIndex] int,[col1] varchar(50))
    Insert Into @YourTable Values
    (1,'apple')
    ,(2,'1')
    ,(3,'red')
    ,(4,'pear')
    ,(5,'2')
    ,(6,'yellow')

    Select [FruitType] = max(case when col=1 then Col1 end)
    ,[Weight] = max(case when col=2 then Col1 end)
    ,[Color] = max(case when col=0 then Col1 end)
    From (
    Select *
    ,Grp = (Row_Number() over (order by [RowIndex]) - 1) / 3
    ,Col = Row_Number() over (order by [RowIndex]) % 3
    From @YourTable
    ) A
    Group By Grp


    Returns



    FruitType Weight Color
    apple 1 red
    pear 2 yellow


    NOTE:



    If RowIndex is truly sequential, you can remove the row_number() function and simply use RowIndex







    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered Nov 12 '18 at 16:12









    John CappellettiJohn Cappelletti

    45.4k62446




    45.4k62446












    • Thanks. Yes the RowIndex is truly sequential.

      – Avithohol
      Nov 12 '18 at 16:36






    • 1





      @Avithohol Great! Then you can replace Row_Number() over (order by [RowIndex]) with [RowIndex]

      – John Cappelletti
      Nov 12 '18 at 16:38

















    • Thanks. Yes the RowIndex is truly sequential.

      – Avithohol
      Nov 12 '18 at 16:36






    • 1





      @Avithohol Great! Then you can replace Row_Number() over (order by [RowIndex]) with [RowIndex]

      – John Cappelletti
      Nov 12 '18 at 16:38
















    Thanks. Yes the RowIndex is truly sequential.

    – Avithohol
    Nov 12 '18 at 16:36





    Thanks. Yes the RowIndex is truly sequential.

    – Avithohol
    Nov 12 '18 at 16:36




    1




    1





    @Avithohol Great! Then you can replace Row_Number() over (order by [RowIndex]) with [RowIndex]

    – John Cappelletti
    Nov 12 '18 at 16:38





    @Avithohol Great! Then you can replace Row_Number() over (order by [RowIndex]) with [RowIndex]

    – John Cappelletti
    Nov 12 '18 at 16:38













    2














    You can try to use ROW_NUMBER window function with some calculation



    insert into SecondTable ( FruitType,Weight,Color)
    SELECT FruitType,Weight,Color
    FROM (
    SELECT MAX(CASE WHEN rn % 3 = 1 THEN [col1] END) FruitType,
    MAX(CASE WHEN rn % 3 = 2 THEN [col1] END) Weight,
    MAX(CASE WHEN rn % 3 = 0 THEN [col1] END) Color
    FROM (
    SELECT *,ROW_NUMBER() OVER(ORDER BY RowIndex) rn
    FROM FirstTable
    ) tt
    GROUP BY (rn - 1) /3
    )tt


    sqlfiddle






    share|improve this answer





























      2














      You can try to use ROW_NUMBER window function with some calculation



      insert into SecondTable ( FruitType,Weight,Color)
      SELECT FruitType,Weight,Color
      FROM (
      SELECT MAX(CASE WHEN rn % 3 = 1 THEN [col1] END) FruitType,
      MAX(CASE WHEN rn % 3 = 2 THEN [col1] END) Weight,
      MAX(CASE WHEN rn % 3 = 0 THEN [col1] END) Color
      FROM (
      SELECT *,ROW_NUMBER() OVER(ORDER BY RowIndex) rn
      FROM FirstTable
      ) tt
      GROUP BY (rn - 1) /3
      )tt


      sqlfiddle






      share|improve this answer



























        2












        2








        2







        You can try to use ROW_NUMBER window function with some calculation



        insert into SecondTable ( FruitType,Weight,Color)
        SELECT FruitType,Weight,Color
        FROM (
        SELECT MAX(CASE WHEN rn % 3 = 1 THEN [col1] END) FruitType,
        MAX(CASE WHEN rn % 3 = 2 THEN [col1] END) Weight,
        MAX(CASE WHEN rn % 3 = 0 THEN [col1] END) Color
        FROM (
        SELECT *,ROW_NUMBER() OVER(ORDER BY RowIndex) rn
        FROM FirstTable
        ) tt
        GROUP BY (rn - 1) /3
        )tt


        sqlfiddle






        share|improve this answer















        You can try to use ROW_NUMBER window function with some calculation



        insert into SecondTable ( FruitType,Weight,Color)
        SELECT FruitType,Weight,Color
        FROM (
        SELECT MAX(CASE WHEN rn % 3 = 1 THEN [col1] END) FruitType,
        MAX(CASE WHEN rn % 3 = 2 THEN [col1] END) Weight,
        MAX(CASE WHEN rn % 3 = 0 THEN [col1] END) Color
        FROM (
        SELECT *,ROW_NUMBER() OVER(ORDER BY RowIndex) rn
        FROM FirstTable
        ) tt
        GROUP BY (rn - 1) /3
        )tt


        sqlfiddle







        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Nov 12 '18 at 16:23

























        answered Nov 12 '18 at 16:17









        D-ShihD-Shih

        25.6k61531




        25.6k61531



























            draft saved

            draft discarded
















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid


            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.

            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53265799%2fpivot-and-insert-every-n-rows%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Darth Vader #20

            How to how show current date and time by default on contact form 7 in WordPress without taking input from user in datetimepicker

            Ondo