[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

One Colum Casuing Multiple Rows

Posted on 2014-08-27
7
Medium Priority
?
122 Views
Last Modified: 2014-09-10
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?
0
Comment
Question by:mike1142
  • 3
  • 2
  • 2
7 Comments
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40289652
Hi,

Can you provide some sample for your data that how it looks and what you want as output. That would help great
0
 
LVL 2

Accepted Solution

by:
Priya Sudharsan earned 1500 total points
ID: 40289656
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
 

Author Comment

by:mike1142
ID: 40289657
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 2

Assisted Solution

by:Priya Sudharsan
Priya Sudharsan earned 1500 total points
ID: 40289659
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
 
LVL 15

Assisted Solution

by:Vikas Garg
Vikas Garg earned 500 total points
ID: 40289662
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
 

Author Comment

by:mike1142
ID: 40290212
How about one row with colors in a comma separated list?
0
 

Author Closing Comment

by:mike1142
ID: 40315415
Thanks. Worked fine.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question