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

Question

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.

Answer 1

0

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

answered Oct 12 '12 at 12:56 PM

eghetto
1.5k ● 6 ● 13 ● 17

incorrect syntax near max

Oct 12 '12 at 01:04 PM Mamzy

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

Oct 12 '12 at 01:09 PM eghetto

(comments are locked)

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

Follow this question

Related Questions