Select to Return Multiple Rows from Single Row

Question

I am working on a query that returns a list of items shown below:

SELECT ID, Description FROM TableA WHERE ID = 10001

Returns:

ID     Description
10001  Item1 Item2 Item3 Item4

However, what I want is to be able to make the return multiple rows.

ID     Description
10001  Item1 
10001  Item2 
10001  Item3 
10001  Item4

Is there an easy way to achieve this?

Thank you!

this makes no sense to me! how are your data stored? do you have 4 different records with ID=10001? if so, then this select SHOULD return 4 rows. it wouldn't combine them in 1. — Skaros Ilias
– Skaros Ilias, Commented May 26, 2015 at 21:28
If you've found any way, easy or hard, I would suggest that method to definitely split the data and store it like that, on multiple rows, with one master record and multiple detail records for the items. — GolezTrol
– GolezTrol, Commented May 26, 2015 at 21:28
I would suggest making a function that splits them using a space as a delimiter. — apkisbossin
– apkisbossin, Commented May 26, 2015 at 21:29
Some kind people have already provided you with technical solutions, but as some of the comments above are trying to say, you are doing it wrong! It is misusing the database to store multiple values (items) within a column (description). The whole point of a database is to make your data accessible. What if you wanted only the id's where the item = "ABC"? You would again need convoluted processing. Please reconsider your design, and store the data as in your "what I want", with ID repeated and one item per row. — Turophile
– Turophile, Commented May 26, 2015 at 22:44

Giorgi Nakeuri · Accepted Answer · 2015-05-26 21:42:37Z

4

Here is how you can do this:

SELECT id,
   Split.a.value('.', 'VARCHAR(100)') AS String  
FROM  (SELECT id, CAST ('<M>' + REPLACE(description, ' ', '</M><M>') + '</M>' AS XML) AS n from TableA where id = 1001) AS A 
CROSS APPLY n.nodes ('/M') AS Split(a);

Fiddle http://sqlfiddle.com/#!3/152e3/1

edited May 26, 2015 at 21:42

answered May 26, 2015 at 21:35

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

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

Comments

BICube · Accepted Answer · 2015-05-26 21:39:42Z

You can also create a function to do that as such

CREATE FUNCTION [dbo].[RowToManyRows] ( @StringInput VARCHAR(8000) )
RETURNS @OutputTable TABLE ( [String] VARCHAR(10) )
AS
BEGIN

    DECLARE @String    VARCHAR(10)

    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(' ', @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(' ', @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO @OutputTable ( [String] )
        VALUES ( @String )
    END

    RETURN
END
GO

You then query that function as such

SELECT ID, String
FROM myTable CROSS APPLY [dbo].RowToManyRows(myTable.Description)

Example:

CREATE TABLE myTable 
( ID INT, 
   Description VARCHAR(50)
 )

 INSERT INTO dbo.myTable
         ( ID, Description )
 VALUES  ( 1, -- ID - int
           'A B'  -- Description - varchar(50)
         ),
         (2, 
         'C D')

Results

Collectives™ on Stack Overflow

Select to Return Multiple Rows from Single Row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related