0

Sql is not my strong suit... Consider the following five tables...

   CREATE TABLE SMTickets (
id INT PRIMARY KEY IDENTITY,
summary NVARCHAR(MAX)
)

CREATE TABLE CUSTOMCONTROL (
    [id] INT PRIMARY KEY IDENTITY,
    [label] NVARCHAR(MAX),
)

CREATE TABLE CUSTOMCONTROL_RESPONSE (
    [id] INT PRIMARY KEY IDENTITY,
    [customcontrolid] INT CONSTRAINT FK_CUSTOMCONTROL_RESPONSE_customcontrolid FOREIGN KEY (customcontrolid)     
    REFERENCES CUSTOMCONTROL(id)
)

CREATE TABLE CUSTOMCONTROL_RESPONSE_VALUES (
    [id] INT PRIMARY KEY IDENTITY,
    [responseid] INT CONSTRAINT FK_CUSTOMCONTROL_RESPONSE_VALUES_responseid FOREIGN KEY (responseid) REFERENCES CUSTOMCONTROL_RESPONSE(id),
    [value] NVARCHAR(MAX)
)

CREATE TABLE CUSTOMCONTROL_RESPONSE_TICKET_ASSIGNMENT (
    [id] INT PRIMARY KEY IDENTITY,
    [responseid] INT CONSTRAINT FK_CUSTOMCONTROL_RESPONSE_TICKET_ASSIGNMENT_responseid FOREIGN KEY (responseid)     
    REFERENCES CUSTOMCONTROL_RESPONSE(id),
    [ticketid] INT CONSTRAINT FK_CUSTOMCONTROL_RESPONSE_TICKET_ASSIGNMENT_ticketid FOREIGN KEY (ticketid) 
    REFERENCES SMTickets(id)
)

INSERT INTO SMTickets (Summary) VALUES ('summary description')
INSERT INTO CUSTOMCONTROL (label) VALUES ('SINGLE')
INSERT INTO CUSTOMCONTROL (label) VALUES ('MULTIPLE')
INSERT INTO CUSTOMCONTROL_RESPONSE (customcontrolid) VALUES (1)
INSERT INTO CUSTOMCONTROL_RESPONSE (customcontrolid) VALUES (2)
INSERT INTO CUSTOMCONTROL_RESPONSE_VALUES(responseid, value) VALUES (1, 'single option')
INSERT INTO CUSTOMCONTROL_RESPONSE_VALUES(responseid, value) VALUES (2, 'option 1')
INSERT INTO CUSTOMCONTROL_RESPONSE_VALUES(responseid, value) VALUES (2, 'option 2')
INSERT INTO CUSTOMCONTROL_RESPONSE_TICKET_ASSIGNMENT(responseid, ticketid) VALUES(1, 1)
INSERT INTO CUSTOMCONTROL_RESPONSE_TICKET_ASSIGNMENT(responseid, ticketid) VALUES(2, 1)
INSERT INTO CUSTOMCONTROL_RESPONSE_TICKET_ASSIGNMENT(responseid, ticketid) VALUES(2, 1)

There can be many response values to a response... When I execute the following query

declare @custom nvarchar(max)
declare @sql nvarchar(max)

set @custom = STUFF((
            SELECT ' , min(Case label WHEN ''' +  (label)  + ''' THEN value END) ' +  QUOTENAME(label) 
            FROM CUSTOMCONTROL
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @sql = '
select smtickets.id, summary
' + @custom + '
from smtickets
left join CUSTOMCONTROL_RESPONSE_TICKET_ASSIGNMENT ta on ta.ticketid = smtickets.id
left join CUSTOMCONTROL_RESPONSE r on r.id = ta.responseid
left join CUSTOMCONTROL_RESPONSE_VALUES rv on rv.responseid = r.id
left join CUSTOMCONTROL c on c.id = r.customcontrolid
group by smtickets.id, summary
order by smtickets.id '

exec(@sql)

I get the following result

ticket_id   summary                 Single          Multiple
3335     [Asset Monitoring Events]  Medium Impact   Option 1

I would like to get the following result

ticket_id   summary                 Single          Multiple
3335     [Asset Monitoring Events]  Medium Impact   Option 1, Option 2

I've tried adding a join on the values but that didn't work...

NHNick603
  • 124
  • 5
  • 1
    Don't forget, we don't have access to your SQL Server, we can't run that SQL. Could you post some DDL and Consumable (in the form of an `INSERT`) Sample Data – Thom A Dec 14 '17 at 17:13
  • You don't need dynamic sql here. But without the details as already requested it is difficult to provide a working example. – Sean Lange Dec 14 '17 at 17:19
  • The hoops you're jumping through and the difficulties you're having should be an indication that this is generally a poor option in relation databases and SQL. May I ask *why* it is that you want to do this? There are likely better options to solve your actual *(underlying)* problem. – MatBailie Dec 14 '17 at 17:31
  • Hi I added the statements... The structure is to allow end users to add there own dynamic columns to tickets – NHNick603 Dec 14 '17 at 17:35
  • What are expecting as output from the newly posted sample data? – Sean Lange Dec 14 '17 at 17:36
  • This has been asked a thousand times before - google or search this site for "sql server rows to csv" – Caius Jard Dec 14 '17 at 17:38
  • Possible duplicate of [Concatenate many rows into a single text string?](https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) – Caius Jard Dec 14 '17 at 17:38
  • @NHNick603 - The structure is fine, but this type of processing appears to be for presentation to a human. You're much better advised keeping your presentation layer and your data layer separated. Keep to the normalised and flexible form when dealing with SQL, transpose and format the results when they reach your application. *(If you ever need to write another SQL process that will consume the data-set you're generating you'll wish you never did this.)* – MatBailie Dec 14 '17 at 17:42

0 Answers0