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
ENDCREATE 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 #testEnvironmentSELECT 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
ENDCREATE 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 #testEnvironmentSELECT 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
ENDCREATE 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 #testEnvironmentSELECT 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
ENDCREATE 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 #testEnvironmentSELECT 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