CODEWITHSUNDEEP
sql.netsql-server-2008c#-4.0
Nelson Prajapati
Nelson Prajapati

Reputation: 13

Get 3 rows data in 1 row

My records looks like:

aid  cmpyname  rid  imgpath

 1      abc     1   ~/img/aa.jpg:~/img/bb.jpg:~/img/cc.jpg:
 2      abc     1   ~/img/dd.jpg:~/img/ee.jpg:~/img/ff.jpg:

 3      xyz     2   ~/img/gg.jpg:~/img/hh.jpg:~/img/ii.jpg:
 4      xyz     2   ~/img/jj.jpg:~/img/kk.jpg:~/img/ll.jpg:

What I want to get is displayed below - but in a single query

cmpyname  rid  imgpath

  abc     1   ~/img/aa.jpg:~/img/bb.jpg:~/img/cc.jpg:~/img/dd.jpg:~/img/ee.jpg:~/img/ff.jpg:

  xyz     2   ~/img/gg.jpg:~/img/hh.jpg:~/img/ii.jpg:~/img/jj.jpg:~/img/kk.jpg:~/img/ll.jpg:

How can I do so? I haven't provided any relationship so please keep it in mind.

Thanks and Regards

Upvotes: 1

Views: 147

Answers (4)

daniel
daniel

Reputation: 1070

select max(cmpyname) as cmpyname, rid,
stuff((
    select imgpath
    from Records t1
    where t1.rid = t2.rid
    order by t1.aid
    for xml path(''), type).value('.', 'varchar(max)'), 1, 0, ''
) as imgpath
from Records t2
group by rid;

http://sqlfiddle.com/#!18/46822/1

Upvotes: 3

MSL
MSL

Reputation: 1010

SELECT DISTINCT B.cmpyname, B.rid,
(
    SELECT imgpath + '' FROM tbl1 AS A WHERE (B.rid = A.rid) FOR XML PATH('')
) AS imgpath

FROM tbl1 AS B

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Something like this:

Just paste into into an empty query window and execute. Adapt to your needs...

DECLARE @tbl TABLE(aid INT,  cmpyname VARCHAR(100),  rid INT,  imgpath VARCHAR(1000));
INSERT INTO @tbl VALUES
 (1,'abc',1,'~/img/aa.jpg:~/img/bb.jpg:~/img/cc.jpg:')
,(2,'abc',1,'~/img/dd.jpg:~/img/ee.jpg:~/img/ff.jpg:')
,(3,'xyz',2,'~/img/gg.jpg:~/img/hh.jpg:~/img/ii.jpg:')
,(4,'xyz',2,'~/img/jj.jpg:~/img/kk.jpg:~/img/ll.jpg:');

SELECT DISTINCT tbl.cmpyname
      ,tbl.rid
      ,(
            SELECT '' + x.imgpath
            FROM @tbl AS x
            WHERE x.rid=tbl.rid
            FOR XML PATH('')
       ) AS allPaths
FROM @tbl AS tbl

Upvotes: 1

Mike Clark
Mike Clark

Reputation: 1870

select distinct rid,imgpath,rid from(
select imgpath=(select imgpath + ', ' AS 'data()' 
FROM tbl_Temp 
where tbl_Temp.rid=rid   FOR XML PATH('')  ) 
,id,cmpyname,rid from tbl_Temp
) as temp

Upvotes: 1

Related Questions