Conversion failed when converting the varchar value ', ' to data type tinyint.

  • seubanks00

    Mr or Mrs. 500

    Points: 591

    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!

  • Sue_H

    SSC Guru

    Points: 90286

    seubanks00 - Thursday, February 14, 2019 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!

    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

  • ryanbesko

    Ten Centuries

    Points: 1275

    seubanks00 - Thursday, February 14, 2019 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!

    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.

  • seubanks00

    Mr or Mrs. 500

    Points: 591

    Sue_H - Thursday, February 14, 2019 1:48 PM

    seubanks00 - Thursday, February 14, 2019 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!

    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

    That worked - Thank you very much!

  • seubanks00

    Mr or Mrs. 500

    Points: 591

    ryanbesko - Thursday, February 14, 2019 1:49 PM

    seubanks00 - Thursday, February 14, 2019 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!

    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.

  • drew.allen

    SSC Guru

    Points: 76631

    ryanbesko - Thursday, February 14, 2019 1:49 PM

    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.

    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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply