CODEWITHSUNDEEP
sqlsql-servert-sql
Robbas
Robbas

Reputation: 39

Add column to SQL result with unique rows

I have an query where I get a comma seperated string as result, such as;

n,n,n,n

where n can be 0 or 1, and will always be the length of four digits.

I use CROSS APPLY STRING_SPLIT to get the result as rows. I would like to add a column to this result, and on each row have an unique string, which would be one word.

Like:

Value|String
1    | description1
0    | description2
1    | description3
1    | description4

I have googled a lot, but can't seem to find how to do this. I hope it is as easy as something like:

SELECT myResultAsRows.Value, {'a','b','c','d'} AS String
FROM table
CROSS APPLY STRING_SPLIT ...
WHERE ...

I know this seems strange, but on another forum (specific for the tool) they suggested hard-coding it... I also know it might depend on the server used, but in general, is something like this doable?

As of right now, the query is this:

SELECT tagValueRow.Value
FROM t_objectproperties tag
CROSS APPLY STRING_SPLIT(tag.Value,',') tagValueRow
WHERE tag.Object_ID = #OBJECTID# AND tag.Property = 'myTagName'

which results in

Value
1
0
1
1

for the specified #OBJECTID#.

Thank you!

edit: made the question more detailed, with example closer to reality.

Upvotes: 0

Views: 197

Answers (3)

ZeroWorks
ZeroWorks

Reputation: 1638

I think using ROW_NUMBER() and SUBSTRING it can be acomplished easily.

Somethink like:

SELECT TOP 26 SUBSTRING('abcdefghijklmnopqrstuvwxyz', 
ROW_NUMBER() OVER (ORDER BY sort_field), 1), *
FROM table

It has it limitation: the lenght of 'abc..' string, but with TOP will avoid errors.

Update

It can be done in the same way using the same approach of ROW_NUMBER and a JOIN:

SELECT TOP 5 T.Value, D.Label
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY Field) AS Position, tagValueRow.Value AS Value
    FROM t_objectproperties tag
    CROSS APPLY STRING_SPLIT(tag.Value,',') tagValueRow
    WHERE tag.Object_ID = #OBJECTID# AND tag.Property = 'myTagName') T
LEFT JOIN (
    VALUES 
    (1, 'description1'), 
    (2, 'description2'), 
    (3, 'description3'), 
    (4, 'description4'), 
    (5, 'description5')) D(Position, Label) ON T.Position=D.Position

Upvotes: 1

Charlieface
Charlieface

Reputation: 72480

If all you need is for each split row to have a value against it in no particular order, then we can cross-join a VALUES table to it based on row-number:

SELECT tagValueRow.Value, desc.description
FROM t_objectproperties tag
CROSS APPLY (
    SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rn
    FROM STRING_SPLIT(tag.Value,',') tagValueRow
) tagValueRow
INNER JOIN (VALUES       -- or LEFT JOIN
    ('desciption1', 1),
    ('desciption2', 2),
    ('desciption3', 3),
    ('desciption4', 4),
) desc (description, rn) ON desc.rn = tagValueRow.rn

WHERE tag.Object_ID = #OBJECTID# AND tag.Property = 'myTagName'

If you may have more than 4 split avlues, but only want a description against the first 4, change the INNER JOIN to LEFT

Upvotes: 1

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

CREATE TABLE Testdata
(
    ID INT,
    String VARCHAR(MAX)
)
CREATE TABLE TestList
(
    ID INT,
    String VARCHAR(MAX)
)

INSERT Testdata SELECT 1,'1,0,1,1'

INSERT TestList SELECT 1,'a,b,c,d'

;WITH tmp(ID,DataItem, String) AS
(
    SELECT
        ID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM Testdata
    UNION ALL

    SELECT
        ID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM tmp
    WHERE
        String > ''
),
 tmp2(ID,DataItem, String) AS  (


    SELECT
        ID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM TestList
    UNION ALL

    SELECT
        ID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM tmp2
    WHERE
        String > ''
)





SELECT 
    p.DataItem,q.DataItem
FROM tmp AS p
CROSS APPLY
(SELECT * FROM tmp2) AS q
--ORDER BY SomeID

DataItem | DataItem
:------- | :-------
1        | a       
0        | a       
1        | a       
1        | a       
1        | b       
0        | b       
1        | b       
1        | b       
1        | c       
0        | c       
1        | c       
1        | c       
1        | d       
0        | d       
1        | d       
1        | d

db<>fiddle here

Upvotes: 1

Related Questions