One Colum Casuing Multiple Rows

I have Col1, col2 and col 3.

Col 3 has multiple values where col1 and 2 do not. I need something in col3 but either row would do.

How can I fix this?
mike1142Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

Can you provide some sample for your data that how it looks and what you want as output. That would help great
0
Priya SudharsanProgrammer AnalystCommented:
Below query will get yo the max col3 value. As you said any col3 value, i assume it as max.
CREATE TABLE #TMP(COL1 INT,COL2 INT, COL3 CHAR(1))

INSERT INTO #TMP SELECT 1,2,'3'
INSERT INTO #TMP SELECT 1,2,'4'
INSERT INTO #TMP SELECT 1,2,'5'

INSERT INTO #TMP SELECT 1,3,'4'
INSERT INTO #TMP SELECT 1,3,'5'
INSERT INTO #TMP SELECT 1,3,'6'
INSERT INTO #TMP SELECT 1,3,'6'

SELECT COL1,COL2,MAX(COL3) FROM #TMP GROUP BY COL1,COL2

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mike1142Author Commented:
I  was being lazy. I'll bet a row rank thing will work

Example

Cols are
Name
Number
Colors

Joe likes green and red

Row 1: joe 1001 red
Row 2: joe 1001 green

I'd don't care if joe likes green and red just that joe likes a color and what it is.

What I want is one row for joe with one of the.e colors he likes but just one row.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Priya SudharsanProgrammer AnalystCommented:
Example:
CREATE TABLE #TMP(Name VARCHAR(30),Number INT, Color CHAR(10))

INSERT INTO #TMP SELECT 'Peter',2,'Red'
INSERT INTO #TMP SELECT 'Peter',2,'Blue'
INSERT INTO #TMP SELECT 'Peter',2,'Green'

INSERT INTO #TMP SELECT 'John',3,'Gray'
INSERT INTO #TMP SELECT 'John',3,'Orange'
INSERT INTO #TMP SELECT 'John',3,'Yellow'
INSERT INTO #TMP SELECT 'John',3,'White'

SELECT Name,Number,MAX(Color) FROM #TMP GROUP BY Name,Number

Open in new window

0
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

You can simply write

 Select  Name, Number, MAX(Colors) Colors  from Table Group By Name, Number

Open in new window

OR
select Name, Number, Colors from 
(
Select  Name, Number, Colors,Row_number() over (partition by Name,Number order by Name ) rn  from Table
) Tbl where tn = 1

Open in new window

0
mike1142Author Commented:
How about one row with colors in a comma separated list?
0
mike1142Author Commented:
Thanks. Worked fine.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.