CODEWITHSUNDEEP
sqlsql-servert-sql
Kelvin Yong
Kelvin Yong

Reputation: 80

Coverting row data into a line of string

How do I convert row data into a line of string as shown below. Thanks in advance.

From this

Coffee Milk Juice
15 5 10

To This

Details
Coffee: 15; Milk: 5; Juice: 10;

I have tried using TRANSLATE() but its not getting anywhere close to what I want.

Upvotes: 1

Views: 110

Answers (3)

Christopher Thompson
Christopher Thompson

Reputation: 179

You can try with:

SELECT
'Coffee: '+ CAST(coffee as varchar(your_length)) +
'; Milk: ' + CAST(milk as varchar(your_length)) + 
'; Juice: ' + CAST(juice as varchar(your_length))
FROM example

Upvotes: 4

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22321

Here is a generic way how to handle dynamic column names. They are not hard-coded.

XML, XQuery, and its FLWOR expression to the rescue.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Coffee INT, Milk INT, Juice INT);
INSERT INTO @tbl (Coffee, Milk, Juice) VALUES
(15, 5, 10),
(25, 7, 18);
-- DDL and sample data population, end

SELECT p.ID
    , (
    SELECT * FROM @tbl AS c
    WHERE c.id = p.ID
    FOR XML PATH('r'), TYPE, ROOT('root')
).query('
    for $x in /root/r/*[local-name()!="ID"]
    return concat(local-name($x)[1], ":", $x/text()[1], ";")
').value('.', 'VARCHAR(MAX)') AS Result
FROM @tbl AS p
GROUP BY p.ID;

Output

+----+------------------------------+
| ID |            Result            |
+----+------------------------------+
|  1 | Coffee:15; Milk:5; Juice:10; |
|  2 | Coffee:25; Milk:7; Juice:18; |
+----+------------------------------+

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

A much better way to write this query uses concat():

SELECT CONCAT('Coffee: ', coffee, '; Milk: ', milk, '; Juice: ', juice)
FROM example

Upvotes: 0

Related Questions