Merging rows and keeping unique values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Merging rows and keeping unique values

Author

Topic

lejanco
Starting Member

8 Posts

Posted - 2015-01-14 : 12:53:31

Hello,

I have this query and it works except for I am getting duplicate primary keys with unique column value. I want to combine them so that I have one primary key, but keep all the columns. Example:
Key column 1 column 2 column 3 column 4
A 1 1
A 2 2
B 2 3
B 5 5

it should look like:

A 1 1 2 2
B 2 3 5 5

Here is my query:
SELECT *
FROM [TLC Inventory].dbo.['2014 new$']
WHERE [TLC Inventory].dbo.['2014 new$'].mis_key LIKE '2%'
AND dbo_Product_Info#description NOT LIKE 'NR%'
AND dbo_Line_Info#description NOT LIKE 'OBSOLETE%'

Do I use a sum function? tHANKS!

Laura Janco

lejanco
Starting Member

8 Posts

Posted - 2015-01-14 : 12:57:14

quote:
Originally posted by lejanco

Hello,

I have this query and it works except for I am getting duplicate primary keys with unique column value. I want to combine them so that I have one primary key, but keep all the columns. Example:
Key column 1 column 2 column 3 column 4
A 1 1
A 2 2
B 2 3
B 5 5

it should look like:

A 1 1 2 2
B 2 3 5 5

Here is my query:
SELECT *
FROM [TLC Inventory].dbo.['2014 new$']
WHERE [TLC Inventory].dbo.['2014 new$'].mis_key LIKE '2%'
AND dbo_Product_Info#description NOT LIKE 'NR%'
AND dbo_Line_Info#description NOT LIKE 'OBSOLETE%'

Do I use a sum function? tHANKS!

I should also mention there are many columns!

Laura Janco

Laura Janco

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-14 : 13:10:53

In your sample data, you have two rows of A's and two rows of B's. is that what you expect? Never three or four row's of A's?

In your output you have the values listed after the letter. Do you need those in separate columns or is one column with the values separated by blanks what you need?

lejanco
Starting Member

8 Posts

Posted - 2015-01-14 : 15:30:55

I would like to only have one row (per sku/primary key) and keep all the columns. Sorry it did not paste correctly
WRONG 1 2 3 4
A 1 1
A 4 2
B 2 2
B 3 3

RIGHT 1 2 3 4
A 1 1 4 2
B 2 2 3 3

THE 1 2 3 4 represent the columns at the top of each example

Laura Janco

lejanco
Starting Member

8 Posts

Posted - 2015-01-14 : 15:33:47

That still didn't post correctly. Basically on the top example (wrong), there are two of the same primary keys. the first A has data in columns 1, 2 and nothing in columns 3, 4. The second A has no data in columns 1,2 and data in columns 3,4. I want it to look like one line with data in all columns.

Thanks,
Laura

Laura Janco

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-14 : 15:38:23

ok, so this might do it


select key, max(col1), max(col2), max(col3), max(col4)
from mytable
group by key

lejanco
Starting Member

8 Posts

Posted - 2015-01-14 : 18:29:56

I see. but I have almost 54 columns. Is there a shorter way than typing out every column? Thanks!

Laura Janco

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-15 : 00:56:05

Why type them? Drag and drop in ssms.

You could work up a dynamic solution but it will be more complicated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources