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

avatar image

Mamzy
10 3 4 5

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

avatar image

eghetto
2.1k 18 23 30

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.

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:

x2070
x425

asked: Oct 12, 2012 at 12:48 PM

Seen: 789 times

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

Copyright 2016 Redgate Software. Privacy Policy