CODEWITHSUNDEEP
sqlsql-server
Kirill Rodeonov
Kirill Rodeonov

Reputation: 145

create one row from two columns sql

How to create one row from two columns?

Example:

id    description
------------------
1      one
2      two
3      three

In result:

1: one, 2: two, 3: three

I use follow statment:

select Id,
  stuff((SELECT distinct ', ' + cast(Description as varchar(10))
           FROM dbo.tbl t2
           where t2.Id = t1.Id
           FOR XML PATH('')),1,1,'') 
from dbo.tbl t1
group by Id

But in result I have two columns. I need one such as string

Upvotes: 2

Views: 128

Answers (4)

Ravi
Ravi

Reputation: 1172

i think you are asking for this

select stuff((SELECT ', ' + CAST(tbl.id AS varchar) + ':' + tbl.description
FROM tablename tbl FOR XML PATH('')), 1, 1, '')  as Columnname

Upvotes: 2

Mahdi Aslami Khavari
Mahdi Aslami Khavari

Reputation: 1985

select statement is like a for loop or an iterator and you you need a space to save you data and it's not possible with select only because in the moment select statement only access to a row not previous row and not next row so

please use a scaler-value function

create function test()
return
    nvarchar(max)
as
begin

    declare @s nvarchar(max)

    select @s = concate(@s, id, description)
    from yourTable

    return @s
end

Upvotes: 0

Shiju Shaji
Shiju Shaji

Reputation: 1730

You were close..

declare   @T TABLE  (
  id int,
   description varchar(50)
);

INSERT INTO @T  VALUES (1,'one');
INSERT INTO @T  VALUES (2,'two');
INSERT INTO @T  VALUES (3,'three');

select Id,
  stuff((SELECT distinct ', ' + +cast(id as nvarchar) +':'+description
           FROM @T t2
           where t2.Id = t1.Id
           FOR XML PATH('')),1,1,'') 
from @T t1
group by Id

OR If you want all the ids in a sing row use the below query

select stuff((select ',' +cast(id as nvarchar) +':'+description  
from @T for xml path('')),1,1,'')

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

You can try this query.

CREATE TABLE T (
   id int,
   description varchar(50)
);

INSERT INTO T  VALUES (1,'one');
INSERT INTO T  VALUES (2,'two');
INSERT INTO T  VALUES (3,'three');

Query 1:

select 
      stuff((SELECT ', ' + CAST(t2.ID AS VARCHAR(5)) + ':'+ t2.description 
               FROM t t2
               FOR XML PATH('')),1,1,'')

Results:

|                        |
|------------------------|
|  1:one, 2:two, 3:three |

Upvotes: 2

Related Questions