SQL Server Conversion failed when converting the varchar value '%,' to data type int

q7solyqu 于 2023-04-19 发布在其他

关注(0)|答案(1)|浏览(201)

Below are my tables - my master table Gmaster and child table Tmaster :

create table dbo.GMaster
(
    CourseId char(2),
    CourseName char(3)
);

create table dbo.TMaster
(
    ROLLNO char(5),
    NAME varchar(10),
    ADDRESS varchar(20),
    Course varchar(100)
);

insert into dbo.GMaster 
values ('-1',  'All'),
       ('0',  '' ,'' ),
       ('1',  'A','D'),
       ('3',  'Unassigned',''),
       ('4',  'C','')
       ('7',  'p','s'),

insert into dbo.TMaster 
values ('1', 'RAM', 'RAM ADDRESS', '0'),                      
       ('3', 'HARI', 'HARI ADDRESS', ',1,4'),                         
       ('4', 'JEFF', 'JEFF ADDRESS', '-1'),                   
       ('6', 'DAISY', 'DAISY ADDRESS', ',7');
       ('8', 'Flow', 'Chart', ',3');

This is my expected output:

ROLLNO	NAME	ADDRESS	Course
1	RAM	RAM ADDRESS	Empty value
3	HARI	HARI ADDRESS	'AD','C'
4	JEFF	JEFF ADDRESS	'','AD','Unassigned','C','PS'
6	DAISY	DAISY ADDRESS	'PS'
8	Flow	Flow Chart	'Unassigned'

I tried this code but get an error

Conversion failed when converting the varchar value '%,' to data type int

select 
    SM.ROLLNO,
    SM.NAME, 
    SM.ADDRESS,
    (select 
         ',' + CM.CourseName
     from 
         dbo.GMaster as CM
     where 
         ',' + SM.Course + ',' like '%,' + CM.CourseId + ',%'
     for xml path(''), type).value('substring(text()[1], 2)', 'varchar(max)') as Course
from 
    dbo.[TMaster] as SM;

sql-server

来源：https://stackoverflow.com/questions/76008058/conversion-failed-when-converting-the-varchar-value-to-data-type-int

1条答案

按热度按时间

roejwanj1#

Your CREATE TABLE script has a bunch of errors: commas in the wrong places, incorrect data types and lengths, missing columns.

You really, really need to fix your design. Denormalized tables are hard to work with.

Having said that, you can just split out the values using STRING_SPLIT , join to GMaster , then re-aggregate using STRING_AGG , all in a subquery.

You need some extra logic for the All case: you need to join all rows, but the All row itself should never be joined.

SELECT t.*, 
  Course = (
    SELECT STRING_AGG('''' + g.CourseName + '''', ',')
    FROM STRING_SPLIT(t.Course, ',') s
    JOIN dbo.GMaster g ON (
        g.CourseId = s.value OR
        s.value = '-1')
      AND g.CourseId <> '-1'
  )
FROM dbo.TMaster t;

db<>fiddle

赞(0）回复(0）举报 2023-04-19

我来回答

SQL Server Conversion failed when converting the varchar value '%,' to data type int

1条答案

相关问题

热门标签

最新问答