July 12, 2016 at 11:07 am
Hi,
I have a table like this:
Create table Tbl_Test01
(
Name nvarchar(20),
Item nvarchar(30)
)
;
insert into Tbl_Test01
values
('Car','Honda2016'),
('Car','Honda2017'),
('Car','Honda2018'),
('Tool','Dwwalt2016'),
('Tool','Dwwalt2017'),
('Tool','Dwwalt2018'),
('Tool','Dwwalt2019')
select *
from Tbl_Test01
I'd like to have the value of the column Name show up once and the column Item to be concartenated and seperated with the line break. Plase see attached for the desired result.
Here is what I have worked on so far but it's not quite what I wanted. I think the line break it's either Char(13) or Char(10) but XML will display wierd characters.
SELECT distinct Name, STUFF((
SELECT ' ' + CHAR(10) + Item
FROM Tbl_Test01
FOR XML PATH('')
), 1, 2, '')
AS Item
From Tbl_Test01 a
Please HELP! Thank you very much in advance!
July 12, 2016 at 11:45 am
This is a presentation issue and should be left to the presentation layer.
I am not seeing any strange characters when I run it. XML will entitize CHAR(13) (
), so I use CHAR(10) instead, which you've already done.
If you are viewing this in the SSMS grid, it will replace the line feeds with spaces. If you are viewing it in text output, it will not align subsequent rows. (This is why it's recommended to do this in the presentation layer.)
Drew
Edited to get correct encoding display.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 12, 2016 at 12:27 pm
You can try the following query but not sure whether it is the most efficient way of doing it.
SELECT CASE WHEN RowRank = 1 THEN Name ELSE '' END AS Name, Item
FROM
(
select Name, Item, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Item) AS RowRank
from #TempTable
) AS DT
July 12, 2016 at 12:28 pm
shogunSQL (7/12/2016)
Hi,I have a table like this:
Create table Tbl_Test01
(
Name nvarchar(20),
Item nvarchar(30)
)
;
insert into Tbl_Test01
values
('Car','Honda2016'),
('Car','Honda2017'),
('Car','Honda2018'),
('Tool','Dwwalt2016'),
('Tool','Dwwalt2017'),
('Tool','Dwwalt2018'),
('Tool','Dwwalt2019')
select *
from Tbl_Test01
I'd like to have the value of the column Name show up once and the column Item to be concartenated and seperated with the line break. Plase see attached for the desired result.
Here is what I have worked on so far but it's not quite what I wanted. I think the line break it's either Char(13) or Char(10) but XML will display wierd characters.
SELECT distinct Name, STUFF((
SELECT ' ' + CHAR(10) + Item
FROM Tbl_Test01
FOR XML PATH('')
), 1, 2, '')
AS Item
From Tbl_Test01 a
Please HELP! Thank you very much in advance!
Try this:
SELECT
Name,
STUFF
((
SELECT ' ' + CHAR(10) + Item
FROM Tbl_Test01
FOR XML PATH('')
),1,2,'')
AS Item
From Tbl_Test01 a
GROUP BY name;
if that does not work, try this:
SELECT
Name,
STUFF
((
SELECT ' ' + CHAR(10) + Item
FROM Tbl_Test01
FOR XML PATH(''), TYPE
).value('.[1]','varchar(100)'),1,2,'')
AS Item
From Tbl_Test01 a
GROUP BY name
See this article[/url] to better understand the technique you are using.
-- Itzik Ben-Gan 2001
July 12, 2016 at 2:21 pm
Thank you very much All for your efforts.
I think I got it now. By replaceing ® sign where Char(10) is, The ® sign is used as a trick for our systems to read as new line seperator. Maybe for many other systems too but someone need to test that.
July 12, 2016 at 4:14 pm
shogunSQL (7/12/2016)
Thank you very much All for your efforts.I think I got it now. By replaceing ® sign where Char(10) is, The ® sign is used as a trick for our systems to read as new line seperator. Maybe for many other systems too but someone need to test that.
You should just use the TYPE option of XML to de-entitize.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply