1

I have data

TIPE| NILAI
PSX     10
GE      10
EG      10
SX      20
SXL     20
SZ      20

how to get output like

psx/ge/eg = 10
sx/sxl/sz = 20

and this is my sytax

ISNULL(CAST((SELECT CONVERT(VARCHAR,TIPE) + ' / '        
FROM tabel
FOR XML PATH('')) AS varchar(max)),'')
Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
mahfudin foed
  • 37
  • 6

2 Answers2

1

Try this:

declare @tbl table (TIPE varchar(5), NILAI int);
insert into @tbl values
('PSX', 10),
('GE', 10),
('EG', 10),
('SX', 20),
('SXL', 20),
('SZ', 20);

select NILAI,
       (select TIPE + '/' from @tbl where t.NILAI = NILAI for xml path('')) [TIPE]
from @tbl t
group by NILAI

--for sql server 2017 or newer
select NILAI, string_agg(TIPE, '/') TIPE
from @tbl
group by NILAI
Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
  • STRING_AGG is available from SQL Server 2017, plus it should be `string_agg(TIPE , '/')` **[DEMO](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=59cccf36aa6523aa9e19addbad3e2840)** – Lukasz Szozda Jun 06 '18 at 07:14
  • @lad2025 I think it should work that way also, but thanks for remarks :) – Michał Turczyn Jun 06 '18 at 07:16
0

Try this

DECLARE @tbl table (TIPE varchar(5), NILAI int);
insert into @tbl values
('PSX', 10),
('GE', 10),
('EG', 10),
('SX', 20),
('SXL', 20),
('SZ', 20);
SELECT DISTINCT  STUFF((SELECT '/ '+TIPE 
        FROM @tbl i WHERE i.NILAI=o.NILAI FOR XML PATH ('')),1,1,'')
              + ' = ' + CAST(NILAI AS VARCHAR) AS [RequiredResult] 
FROM @tbl o

Result

RequiredResult
--------------------
 PSX/ GE/ EG = 10
 SX/ SXL/ SZ = 20

Demo: http://rextester.com/WYAJ22189

Sreenu131
  • 2,476
  • 1
  • 7
  • 18