February 14, 2019 at 1:38 pm
I have data that looks like this:
ID | NAME
32 | 14
61 | 3
61 | 14
83 | 2
83 | 12
83 | 3
I need a query to return it like this:
ID | NAME
32 | 14
61 | 3,14
83 | 2,12,3
I am running into the following error when I try:
"Conversion failed when converting the varchar value ', ' to data type tinyint."
Here is the code to recreate:
-- =========
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
CREATE TABLE [dbo].#testEnvironment(
[ID] [int] NOT NULL,
[NAME] [tinyint] NOT NULL,
)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (32,14)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (61,3)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (61,14)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (83,2)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (83,12)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (83,3)
--SELECT *
--FROM #testEnvironment
SELECT ID, STUFF((SELECT ', ' + NAME
FROM #testEnvironment r2
WHERE r2.ID = r1.ID
ORDER BY ID
FOR XML PATH('')), 1, 1, '') AS NAME
FROM #testEnvironment r1
GROUP BY ID
-- ========
My environment is MS SQL 2005. I can't seem to get around this error. Can anyone tell me how to fix this? Is there a better way to go about this? Thank You in advance!
February 14, 2019 at 1:48 pm
seubanks00 - Thursday, February 14, 2019 1:38 PMI have data that looks like this:ID | NAME
32 | 14
61 | 3
61 | 14
83 | 2
83 | 12
83 | 3I need a query to return it like this:
ID | NAME
32 | 14
61 | 3,14
83 | 2,12,3I am running into the following error when I try:
"Conversion failed when converting the varchar value ', ' to data type tinyint."Here is the code to recreate:
-- =========
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
CREATE TABLE [dbo].#testEnvironment(
[ID] [int] NOT NULL,
[NAME] [tinyint] NOT NULL,
)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (32,14)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (61,3)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (61,14)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (83,2)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (83,12)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (83,3)
--SELECT *
--FROM #testEnvironment
SELECT ID, STUFF((SELECT ', ' + NAME
FROM #testEnvironment r2
WHERE r2.ID = r1.ID
ORDER BY ID
FOR XML PATH('')), 1, 1, '') AS NAME
FROM #testEnvironment r1
GROUP BY ID
-- ========
My environment is MS SQL 2005. I can't seem to get around this error. Can anyone tell me how to fix this? Is there a better way to go about this? Thank You in advance!
It's trying to do addition using the integers and a character (the comma). You want it to concatenate so you would want to cast NAME as varchar(2) or whatever length you need. In your sample, something like:
SELECT ID, STUFF((SELECT ', ' + CAST(NAME as varchar(2))
FROM #testEnvironment r2
WHERE r2.ID = r1.ID
ORDER BY ID
FOR XML PATH('')), 1, 1, '') AS NAME
FROM #testEnvironment r1
GROUP BY ID
Sue
February 14, 2019 at 1:49 pm
seubanks00 - Thursday, February 14, 2019 1:38 PMI have data that looks like this:ID | NAME
32 | 14
61 | 3
61 | 14
83 | 2
83 | 12
83 | 3I need a query to return it like this:
ID | NAME
32 | 14
61 | 3,14
83 | 2,12,3I am running into the following error when I try:
"Conversion failed when converting the varchar value ', ' to data type tinyint."Here is the code to recreate:
-- =========
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
CREATE TABLE [dbo].#testEnvironment(
[ID] [int] NOT NULL,
[NAME] [tinyint] NOT NULL,
)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (32,14)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (61,3)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (61,14)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (83,2)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (83,12)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (83,3)
--SELECT *
--FROM #testEnvironment
SELECT ID, STUFF((SELECT ', ' + NAME
FROM #testEnvironment r2
WHERE r2.ID = r1.ID
ORDER BY ID
FOR XML PATH('')), 1, 1, '') AS NAME
FROM #testEnvironment r1
GROUP BY ID
-- ========
My environment is MS SQL 2005. I can't seem to get around this error. Can anyone tell me how to fix this? Is there a better way to go about this? Thank You in advance!
I'm sure there's a more elaborate way to do this but changing SELECT ID, STUFF((SELECT ', ' + NAME to SELECT ID, STUFF((SELECT ', ' + REPLACE(NAME, ',', '') provides the desired results with no error.
February 14, 2019 at 2:05 pm
Sue_H - Thursday, February 14, 2019 1:48 PMseubanks00 - Thursday, February 14, 2019 1:38 PMI have data that looks like this:ID | NAME
32 | 14
61 | 3
61 | 14
83 | 2
83 | 12
83 | 3I need a query to return it like this:
ID | NAME
32 | 14
61 | 3,14
83 | 2,12,3I am running into the following error when I try:
"Conversion failed when converting the varchar value ', ' to data type tinyint."Here is the code to recreate:
-- =========
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
CREATE TABLE [dbo].#testEnvironment(
[ID] [int] NOT NULL,
[NAME] [tinyint] NOT NULL,
)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (32,14)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (61,3)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (61,14)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (83,2)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (83,12)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (83,3)
--SELECT *
--FROM #testEnvironment
SELECT ID, STUFF((SELECT ', ' + NAME
FROM #testEnvironment r2
WHERE r2.ID = r1.ID
ORDER BY ID
FOR XML PATH('')), 1, 1, '') AS NAME
FROM #testEnvironment r1
GROUP BY ID
-- ========
My environment is MS SQL 2005. I can't seem to get around this error. Can anyone tell me how to fix this? Is there a better way to go about this? Thank You in advance!It's trying to do addition using the integers and a character (the comma). You want it to concatenate so you would want to cast NAME as varchar(2) or whatever length you need. In your sample, something like:
SELECT ID, STUFF((SELECT ', ' + CAST(NAME as varchar(2))
FROM #testEnvironment r2
WHERE r2.ID = r1.ID
ORDER BY ID
FOR XML PATH('')), 1, 1, '') AS NAME
FROM #testEnvironment r1
GROUP BY IDSue
That worked - Thank you very much!
February 14, 2019 at 2:06 pm
ryanbesko - Thursday, February 14, 2019 1:49 PMseubanks00 - Thursday, February 14, 2019 1:38 PMI have data that looks like this:ID | NAME
32 | 14
61 | 3
61 | 14
83 | 2
83 | 12
83 | 3I need a query to return it like this:
ID | NAME
32 | 14
61 | 3,14
83 | 2,12,3I am running into the following error when I try:
"Conversion failed when converting the varchar value ', ' to data type tinyint."Here is the code to recreate:
-- =========
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
CREATE TABLE [dbo].#testEnvironment(
[ID] [int] NOT NULL,
[NAME] [tinyint] NOT NULL,
)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (32,14)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (61,3)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (61,14)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (83,2)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (83,12)
INSERT INTO [dbo].#testEnvironment (ID,[NAME])VALUES (83,3)
--SELECT *
--FROM #testEnvironment
SELECT ID, STUFF((SELECT ', ' + NAME
FROM #testEnvironment r2
WHERE r2.ID = r1.ID
ORDER BY ID
FOR XML PATH('')), 1, 1, '') AS NAME
FROM #testEnvironment r1
GROUP BY ID
-- ========
My environment is MS SQL 2005. I can't seem to get around this error. Can anyone tell me how to fix this? Is there a better way to go about this? Thank You in advance!I'm sure there's a more elaborate way to do this but changing SELECT ID, STUFF((SELECT ', ' + NAME to SELECT ID, STUFF((SELECT ', ' + REPLACE(NAME, ',', '') provides the desired results with no error.
Thank you.
February 14, 2019 at 2:08 pm
ryanbesko - Thursday, February 14, 2019 1:49 PMI'm sure there's a more elaborate way to do this but changing SELECT ID, STUFF((SELECT ', ' + NAME to SELECT ID, STUFF((SELECT ', ' + REPLACE(NAME, ',', '') provides the desired results with no error.
This is just a way to obscure the fact that you are converting an integer to a string. You're using a function for it's side effects rather than for it's actual function. It's a bad idea and it makes your code more difficult to read. Just use a simple CAST or CONVERT.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy