locked
Convert rows into single column in SQL RRS feed

  • Question

  • User1910487977 posted
    CREATE TABLE [dbo].[TestOrder](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[OrderQty] [int] NULL)
    
    	Insert into [TestOrder] values(10),(15),(30)
    
    Select ID,[OrderQty], Number+1 as [OrderQty2]  from [TestOrder]
    cross apply (SELECT [number]       
      FROM [master].[dbo].[spt_values]
      where type='p' ) d(number)
      WHERE number<[OrderQty]

    The below is the ouput.
    ID	OrderQty	OrderQty2
    1	10	1
    1	10	2
    1	10	3
    1	10	4
    1	10	5
    1	10	6
    1	10	7
    1	10	8
    1	10	9
    1	10	10
    2	15	1
    2	15	2
    2	15	3
    2	15	4
    2	15	5
    2	15	6
    2	15	7
    2	15	8
    2	15	9
    2	15	10
    2	15	11
    2	15	12
    2	15	13
    2	15	14
    2	15	15
    3	30	1
    3	30	2
    3	30	3
    3	30	4
    3	30	5
    3	30	6
    3	30	7
    3	30	8
    3	30	9
    3	30	10
    3	30	11
    3	30	12
    3	30	13
    3	30	14
    3	30	15
    3	30	16
    3	30	17
    3	30	18
    3	30	19
    3	30	20
    3	30	21
    3	30	22
    3	30	23
    3	30	24
    3	30	25
    3	30	26
    3	30	27
    3	30	28
    3	30	29
    3	30	30
    4	25	1
    4	25	2
    4	25	3
    4	25	4
    4	25	5
    4	25	6
    4	25	7
    4	25	8
    4	25	9
    4	25	10
    4	25	11
    4	25	12
    4	25	13
    4	25	14
    4	25	15
    4	25	16
    4	25	17
    4	25	18
    4	25	19
    4	25	20
    4	25	21
    4	25	22
    4	25	23
    4	25	24
    4	25	25
    5	55	1
    5	55	2
    5	55	3
    5	55	4
    5	55	5
    5	55	6
    5	55	7
    5	55	8
    5	55	9
    5	55	10
    5	55	11
    5	55	12
    5	55	13
    5	55	14
    5	55	15
    5	55	16
    5	55	17
    5	55	18
    5	55	19
    5	55	20
    5	55	21
    5	55	22
    5	55	23
    5	55	24
    5	55	25
    5	55	26
    5	55	27
    5	55	28
    5	55	29
    5	55	30
    5	55	31
    5	55	32
    5	55	33
    5	55	34
    5	55	35
    5	55	36
    5	55	37
    5	55	38
    5	55	39
    5	55	40
    5	55	41
    5	55	42
    5	55	43
    5	55	44
    5	55	45
    5	55	46
    5	55	47
    5	55	48
    5	55	49
    5	55	50
    5	55	51
    5	55	52
    5	55	53
    5	55	54
    5	55	55
    But i need the OrderQty2 value in single column based on ID column like below
    ID   OrderQty
    
    1     10              1 2 3 4 5 6 7 8 9 10
    
    2     15             1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
    
    3     30            1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30



    Saturday, October 5, 2019 5:15 AM

Answers

  • User-2082239438 posted
    CREATE TABLE [dbo].[TestOrder]
    (
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[OrderQty] [int] NULL
    )
    
    Insert into [TestOrder] 
    values(10),(15),(30)
    
    
    SELECT *
    ,STUFF
    (
    	(
    		SELECT ',' + CONVERT(VARCHAR,[number])
    		FROM [master].[dbo].[spt_values] 
    		WHERE type='p' 
    		AND number>0
    		AND number<=TestOrder.OrderQty
    		FOR XML PATH('')), 1, 1, ''
    ) AS OrderQty2 
    FROM TestOrder 
    
    -------------------OUTPUT--------------------------------------
    --ID	OrderQty	OrderQty2
    --1		10			1,2,3,4,5,6,7,8,9,10
    --2		15			1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
    --3		30			1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
    
    DROP TABLE [TestOrder] ;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 5, 2019 6:10 AM

All replies

  • User-2082239438 posted
    CREATE TABLE [dbo].[TestOrder]
    (
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[OrderQty] [int] NULL
    )
    
    Insert into [TestOrder] 
    values(10),(15),(30)
    
    
    SELECT *
    ,STUFF
    (
    	(
    		SELECT ',' + CONVERT(VARCHAR,[number])
    		FROM [master].[dbo].[spt_values] 
    		WHERE type='p' 
    		AND number>0
    		AND number<=TestOrder.OrderQty
    		FOR XML PATH('')), 1, 1, ''
    ) AS OrderQty2 
    FROM TestOrder 
    
    -------------------OUTPUT--------------------------------------
    --ID	OrderQty	OrderQty2
    --1		10			1,2,3,4,5,6,7,8,9,10
    --2		15			1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
    --3		30			1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
    
    DROP TABLE [TestOrder] ;

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 5, 2019 6:10 AM
  • User77042963 posted

    You can use the new function string_agg if you are using SQL Server 2017 or above.

    CREATE TABLE [dbo].[TestOrder](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[OrderQty] [int] NULL)
    
    	Insert into [TestOrder] values(10),(15),(30)
    
    Select ID,[OrderQty], 
    string_agg(Number+1, ' ') as [OrderQty2]  
    from [TestOrder]
    cross apply (SELECT [number]       
      FROM [master].[dbo].[spt_values]
      where type='p' ) d(number)
      WHERE number<[OrderQty]
      Group by ID,[OrderQty]
    
    
      drop TABLE [dbo].[TestOrder]

    Monday, October 7, 2019 6:34 PM