1

I have some problem to get the data like if I have a columns in table

CDPRCL          CDITEM              CDSQFT      F162_U
18-020-0056     SOUND SYS           1.00        H
18-020-0056     WET BAR             2.00        D

as you can see the CDPRCL has two same number but diff data in other columns. I want the data in this format , please see the result for column F162_U

CDPRCL          CDITEM              CDSQFT      F162_U
18-020-0056     SOUND SYS           1.00        H,D
18-020-0056     WET BAR             2.00        H,D

how to get this . please help with this . thanks in advance

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
Mukesh
  • 55
  • 4
  • This is very RDBMS specific. Are you using Sql Server or are you using Mysql? Those are two very different databases. – JNevill Mar 27 '17 at 13:48
  • ho sorry . am using SQL Server – Mukesh Mar 27 '17 at 13:49
  • 2
    How many possible values do you have? Are there always two rows? – Gordon Linoff Mar 27 '17 at 13:52
  • @Mukesh Please let me know if my answer worked for you or if you have any issues with the answer. Please accept my reply as correct answer if it worked for you- so that other users can benefit: from knowing that the answer works and by having the question marked as Answered – DhruvJoshi Mar 28 '17 at 05:03
  • dear Gordon Linoff That was just a sample data . we do have lot of data with different alphabet in the column F162_U – Mukesh Mar 29 '17 at 08:33

2 Answers2

0

Please try a query like this

select 
A.CDPRCL,
A.CDITEM,
A.CDSQFT,
STUFF((
 SELECT ','+ B.F162_U  
 FROM Yourtable B 
 WHERE B.CDPRCL=A.CDPRCL
 ORDER BY B.F162_U 
 FOR XML PATH ('')
 ),1, 1, '') AS  F162_U

from 
Yourtable A
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
0

Select for XML Path as below:

create table TestTable (CDPRCL varchar(20), CDITEM varchar(20), CDSQFT  float, F162_U varchar(10))

Insert into TestTable  values('18-020-0056', 'SOUND SYS', 1.00, 'H')
Insert into TestTable  values('18-020-0056', 'WET BAR', 2.00, 'D')

Select CDPRCL, CDITEM, CDSQFT, substring((Select ', ' + F162_U
            From TestTable Tbl2
            Where Tbl1.CDPRCL = Tbl2.CDPRCL
            For XML PATH ('') ),2,1000)
from TestTable Tbl1
cloudsafe
  • 2,444
  • 1
  • 8
  • 24