how would i find if a field has more than one variable?

Question

question

Mamzy asked Oct 12, '12

how would i find if a field has more than one variable?

hi guys basically iv got a table here with a field called aims and a field called modules. i need to run a query to show if one aim has more than one module in the table. the table looks like this. AIM Module 321 1 234 2 321 2 456 3 567 1 234 1 so i need a query that will show me all these aims with all the modules they have. eg AIM MODULE 321 1,2 234 1,2 456 3 567 1 please help.

sql-server-2008 query

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn_Melton commented · Oct 12, 2012 at 12:56 PM

What have you tried so far?

0 ·

Answer 1 · 2012-10-12T12:56:34Z

eghetto answered Oct 12, '12

SELECT [Aim] ,STUFF(( SELECT ', ' + CAST([Module] AS VARCHAR(MAX)) FROM AIMS WHERE (Aim = Results.Aim) FOR XML PATH ('') ),1,2,'') AS Modules FROM AIMS AS Results GROUP BY Aim

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mamzy commented · Oct 12, 2012 at 01:04 PM

incorrect syntax near max

0 ·

eghetto commented · Oct 12, 2012 at 01:09 PM

works for me: CREATE TABLE #AIMS ( [Aim] INT, [Module] INT ) INSERT INTO #AIMS ([Aim] ,[Module]) VALUES (1 ,4) INSERT INTO #AIMS ([Aim] ,[Module]) VALUES (1 ,8) INSERT INTO #AIMS ([Aim] ,[Module]) VALUES (2 ,9) SELECT [Aim] ,STUFF(( SELECT ', ' + CAST([Module] AS VARCHAR(MAX)) FROM #AIMS WHERE (Aim = Results.Aim) FOR XML PATH ('') ),1,2,'') AS Modules FROM #AIMS AS Results GROUP BY Aim DROP TABLE #AIMS

0 ·

question