x

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.

more ▼

asked Oct 12, 2012 at 12:48 PM in Default

Mamzy gravatar image

Mamzy
10 3 4 4

What have you tried so far?
Oct 12, 2012 at 12:56 PM Shawn_Melton
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
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
more ▼

answered Oct 12, 2012 at 12:56 PM

eghetto gravatar image

eghetto
2.1k 15 17 23

incorrect syntax near max
Oct 12, 2012 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, 2012 at 01:09 PM eghetto
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834
x369

asked: Oct 12, 2012 at 12:48 PM

Seen: 600 times

Last Updated: Oct 13, 2012 at 11:04 AM