How do I query my data with multiple groups

Question

How do I query my data with multiple groups

halifaxdal

SSCoach

Points: 19741
More actions
June 27, 2009 at 6:18 am

#131651

Hi guys,
I have data like this:
ID
Field
Group
1 aaaa
2 bbbb
3 cccc
4 dddd
....
Now, all the data needs to be falgged as different groups, problem is for any one record, it might belong to multiple groups, for example, aaaa might belong to A, B, C and bbbb might belong to B and C, and so on.
The grouping information will be added later on once all my data is populated.
Now, I have some questions before I go ahead:
1. How do I add multiple groups to one record? should I create multiple fields called Group1, Group2, ....?
2. How do I query based on multiple groups since one record might belong to multiple groups.
I hope I explained clearly.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

halifaxdal SSCoach Points: 19741 More actions · Answer 2

Hi Gail,

As I said, the table structure is just like what I posted.

ID

Field1

(Field2...)

...

Sample data:

1 aaaa

2 bbbb

3 cccc

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Please read the article.

I'll put what you gave into the suggest format, but there's a lot of info that I don't have. Also please post your expected results based off the sample data

CREATE TABLE .... (

ID ...

Field1 ...

Field2 ...

...

)

INSERT INTO ... (Id, Field1, Field2, ... ) Values (1, 'aaaa', ...)

INSERT INTO ... (Id, Field1, Field2, ... ) Values (2, 'bbbb', ...)

INSERT INTO ... (Id, Field1, Field2, ... ) Values (3, 'cccc', ...)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

halifaxdal SSCoach Points: 19741 More actions · Answer 4

Never mind Gail, I've figured out. Thank you for your time.

🙂