Solved

manipulating SQL table

Posted on 2014-03-03
13
375 Views
Last Modified: 2014-03-28
Hello! I am trying to accomplish something semi complicated I think with a SQL table I have. I have attached a visual demo in Excel of what I want to do. Basically, I want to search through a table and locate all records with the same FirmName values. If I find any, I want to append the values in field3 thru field7 to the end of the first record. Then I want to delete the records I copied and pasted from, and just keep the first one which I copy and pasted to. So in the attached, I would keep record 2 and delete records 3 and 4. Now I cannot be sure how large these groups can be, sometimes it is a pair, and sometimes it could be as many as 5 or more even. But simply put, I want to stick those values from the subsequent records on the end of the first record and delete those subsequent records. Does anyone know how I can accomplish this?
sample-table.xlsx
0
Comment
Question by:mrosier
  • 6
  • 6
13 Comments
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 39901697
can you post the schema for your table?
0
 

Author Comment

by:mrosier
ID: 39901702
I actually haven't put this data into SQL yet, I have it in Excel format. I was going to import it into SQL to try and do this task using an update query as opposed to doing it by hand in Excel.
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 39901740
As long as you keep the field names the same we can accomplish with some dynamic sql.

Are there only up to 3 rows per firm name?
0
 

Author Comment

by:mrosier
ID: 39901745
sometimes it is just a pair, sometimes it is upwards above 6 rows.
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 39901760
Actually, no need for dynamic SQL.

See the code, I only did 3 rows per company with 2 fields per row, but you should be able to expand for the columns.

Let me know if you have any questions.

create table #temp
(
  field1 int, 
  CompanyId int,
  field3 int,
  field4 int,
  field5 int NULL,
  field6 int NULL,
  field7 int null,
  field8 int null,
)

insert into #temp (field1, CompanyId, field3, field4)
select 1, 1, 3,4

insert into #temp (field1, CompanyId, field3, field4)
select 2, 1, 4,5

insert into #temp (field1, CompanyId, field3, field4)
select 3, 1, 6,7

insert into #temp (field1, CompanyId, field3, field4)
select 1, 2, 5,4

insert into #temp (field1, CompanyId, field3, field4)
select 2, 2, 3,2

insert into #temp (field1, CompanyId, field3, field4)
select 3, 2, 1,0


insert into #temp (field1, CompanyId, field3, field4)
select 3, 3, 9,8


insert into #temp (field1, CompanyId, field3, field4)
select 3, 3, 7,6


--BEFORE
select * from #temp



;with cte as(
select *, row_number() over (partition by CompanyId order by field1) row 
from #temp 
)

update p
set 
--map the fields as needed.
    p.field5 = p2.field3, 
    p.Field6 = p2.field4,
    p.Field7 = p3.field3,
    p.Field8 = p3.field4
from cte p
--keep adding joins for each row.
left join cte p2 on p.CompanyId = p2.CompanyId and p2.row = 2
left join cte p3 on p.CompanyId = p3.CompanyId and p3.row = 3

     ;with cte as(
select *, row_number() over (partition by CompanyId order by field1) row 
from #temp 
)
delete from cte where row != 1
     
-- after     
select * from #temp             

Open in new window

0
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 39901887
Or if you wanted to do it dynamically:

--setup for test
create table #temp
(
  field1 int, 
  CompanyId int,
  field3 int,
  field4 int,
  field5 int NULL,
  field6 int NULL,
  field7 int null,
  field8 int null,
  field9 int NULL,
  field10 int NULL,
  field11 int null,
  field12 int null,
  field13 int NULL,
  field14 int NULL,
  field15 int null,
  field16 int null,
  field17 int null
)

insert into #temp (field1, CompanyId, field3, field4, field5,field6,field7)
select 1, 1, 3,4,5,6,7

insert into #temp (field1, CompanyId, field3, field4, field5,field6,field7)
select 2, 1, 8,9,10,11,12

insert into #temp (field1, CompanyId, field3, field4, field5,field6,field7)
select 3, 1, 13,14,15,16,17

insert into #temp (field1, CompanyId, field3, field4, field5,field6,field7)
select 1, 2, 5,5,5,5,5

insert into #temp (field1, CompanyId, field3, field4, field5,field6,field7)
select 2, 2, 4,4,4,4,4

insert into #temp (field1, CompanyId, field3, field4, field5,field6,field7)
select 3, 2, 3,3,3,3,3


insert into #temp (field1, CompanyId, field3, field4, field5,field6,field7)
select 3, 3, 9,8,7,6,5


insert into #temp (field1, CompanyId, field3, field4, field5,field6,field7)
select 3, 3, 4,3,2,1,0
-- END SETUP

--CHANGE #TEMP TO YOUR TABLE from this point on.
--BEFORE
select * from #temp

;with cte as(
select *, row_number() over (partition by CompanyId order by field1) row 
from #temp 
)


select distinct 
row,
';with cte as(
select *, row_number() over (partition by CompanyId order by field1) row 
from #temp 
)update P set p.Field' + convert(varchar, 3 + (row-1) * 5) + '=p' + convert(varchar, row) +'.Field3 from CTE p left join cte p' +  convert(varchar, row) +  ' on p.CompanyId = p' +  convert(varchar, row) +  '.CompanyId and p' +  convert(varchar, row) +  '.Row = ' + convert( varchar,row) + ';' +
';with cte as(
select *, row_number() over (partition by CompanyId order by field1) row 
from #temp 
)update P set p.Field' + convert(varchar, 3 + (row-1) * 5 + 1) + '=p' + convert(varchar, row) +'.Field4 from CTE p left join cte p' +  convert(varchar, row) +  ' on p.CompanyId = p' +  convert(varchar, row) +  '.CompanyId and p' +  convert(varchar, row) +  '.Row = ' + convert( varchar,row) + ';' +
';with cte as(
select *, row_number() over (partition by CompanyId order by field1) row 
from #temp 
)update P set p.Field' + convert(varchar, 3 + (row-1) * 5 + 2) + '=p' + convert(varchar, row) +'.Field5 from CTE p left join cte p' +  convert(varchar, row) +  ' on p.CompanyId = p' +  convert(varchar, row) +  '.CompanyId and p' +  convert(varchar, row) +  '.Row = ' + convert( varchar,row) + ';' +
';with cte as(
select *, row_number() over (partition by CompanyId order by field1) row 
from #temp 
)update P set p.Field' + convert(varchar, 3 + (row-1) * 5 + 3) + '=p' + convert(varchar, row) +'.Field6 from CTE p left join cte p' +  convert(varchar, row) +  ' on p.CompanyId = p' +  convert(varchar, row) +  '.CompanyId and p' +  convert(varchar, row) +  '.Row = ' + convert( varchar,row) + ';' +
';with cte as(
select *, row_number() over (partition by CompanyId order by field1) row 
from #temp 
)update P set p.Field' + convert(varchar, 3 + (row-1) * 5 + 4) + '=p' + convert(varchar, row) +'.Field7 from CTE p left join cte p' +  convert(varchar, row) +  ' on p.CompanyId = p' +  convert(varchar, row) +  '.CompanyId and p' +  convert(varchar, row) +  '.Row = ' + convert( varchar,row) + ';'  as qry
into #queries
from cte
where row != 1


declare @row int
declare @query varchar(max)
while (select count(*) from #queries) != 0
begin
  select top 1 @row = row, @query = qry from #queries
  EXEC(@query)
  delete from #queries where row = @row
end






     ;with cte as(
select *, row_number() over (partition by CompanyId order by field1) row 
from #temp 
)
delete from cte where row != 1
     
-- after     
select * from #temp             

--clean up
-- drop table #temp
drop table #queries

Open in new window

0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:mrosier
ID: 39903425
ahhh thanks Kyle, do I just put that code all into a new query in SQL Server Management Studio or in some other environment?
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 39903438
SQL server managment studio.  

There are two different approaches.

The first one (http://www.experts-exchange.com/Database/MS-SQL-Server/SQL-Server-2005/Q_28379230.html#a39901760) is a manual approach with a lot of typing.

The second one is a dynamic approach and will execute everything for you.  Just skip the initialization piece (the insert into #temp) and replace #temp with your actual table.

That query is a stand alone fire off method that shows you my approach works.  (EG: you can copy the entire script and run it to see what it's doing).
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39914013
Code below uses dynamic code as well.  

NOTE: if the table columns are large data, the temp table could instead store just the keys and re-read the original table to get the data.


--this just sets up sample data, like your original data, it's not part of the actual solution code;
--I did add another sample FirmName with two more rows of data
--so you can see that the code automatically handles it.
--comment out either FirmName's data and run again and you'll
--see that the final output adjusts itself to match the number of columns needed

IF OBJECT_ID('tempdb..#data') IS NOT NULL
    DROP TABLE #data
create table #data (
    field1 varchar(50),
    FirmName varchar(50),
    field3 varchar(50),
    field4 varchar(50),
    field5 varchar(50),
    field6 varchar(50),
    field7 varchar(50)
    )
--/* firm 'b'
insert into #data values( 'a',      'b',      'c',      'd',      'e',      'f',      'g' )
insert into #data values( 'h',      'b',      'i',      'j',      'k',      'l',      'm' )
insert into #data values( 'n',      'b',      'o',      'p',      'q',      'r',      's' )
--*/
--/* firm 's'
insert into #data values( 'aa',      's',      'cc',      'dd',      'ee',      'ff',      'gg' )
insert into #data values( 'hh',      's',      'ii',      'jj',      'kk',      'll',      'mm' )
insert into #data values( 'nn',      's',      'oo',      'pp',      'qq',      'rr',      'ss' )
insert into #data values( 'tt',      's',      'uu',      'vv',      'ww',      'xx',      'yy' )
insert into #data values( 'zz',      's',      'aaa',      'bbb',      'ccc',      'ddd',      'eee' )
--*/

------------------------------------------------------------------------------------------------------------------------
--solution code begins here---------------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#FirmCombine') IS NOT NULL
    DROP TABLE #FirmCombine
CREATE TABLE #FirmCombine (
    FirmName varchar(50) NOT NULL,
    Row_Number smallint NOT NULL,
    field1 varchar(30) NOT NULL,
    field3 varchar(50) NULL,
    field4 varchar(50) NULL,
    field5 varchar(50) NULL,
    field6 varchar(50) NULL,
    field7 varchar(50) NULL,
    UNIQUE CLUSTERED ( FirmName, Row_Number )
    )

DECLARE @max_rows_per_firmname smallint
DECLARE @sql varchar(max)
DECLARE @sql_build_row_num smallint

INSERT INTO #FirmCombine
SELECT
    FirmName, ROW_NUMBER() OVER (PARTITION BY FirmName ORDER BY field1) AS row_number,
    field1, field3, field4, field5, field6, field7    
FROM #data
ORDER BY
    FirmName, field1 --<<-- change to ORDER BY whatever you need here
   
SELECT @max_rows_per_firmname = MAX(row_number)
FROM #FirmCombine

SET @sql = 'SELECT ' + '
    MAX(CASE WHEN row_number = 1 THEN field1 ELSE '''' END) AS field1,
    FirmName'
SET @sql_build_row_num = 1
WHILE @sql_build_row_num <= @max_rows_per_firmname
BEGIN
    SET @sql = @sql + ',
    MAX(CASE WHEN row_number = ' + CAST(@sql_build_row_num AS varchar(5)) + ' THEN field3 ELSE '''' END) AS field' + CAST(3 + ((@sql_build_row_num - 1) * 5) AS varchar(5)) + ',
    MAX(CASE WHEN row_number = ' + CAST(@sql_build_row_num AS varchar(5)) + ' THEN field4 ELSE '''' END) AS field' + CAST(4 + ((@sql_build_row_num - 1) * 5) AS varchar(5)) + ',
    MAX(CASE WHEN row_number = ' + CAST(@sql_build_row_num AS varchar(5)) + ' THEN field5 ELSE '''' END) AS field' + CAST(5 + ((@sql_build_row_num - 1) * 5) AS varchar(5)) + ',
    MAX(CASE WHEN row_number = ' + CAST(@sql_build_row_num AS varchar(5)) + ' THEN field6 ELSE '''' END) AS field' + CAST(6 + ((@sql_build_row_num - 1) * 5) AS varchar(5)) + ',
    MAX(CASE WHEN row_number = ' + CAST(@sql_build_row_num AS varchar(5)) + ' THEN field7 ELSE '''' END) AS field' + CAST(7 + ((@sql_build_row_num - 1) * 5) AS varchar(5))
    SET @sql_build_row_num = @sql_build_row_num + 1
END --WHILE
SET @sql = @sql + '
    FROM #FirmCombine
    GROUP BY FirmName
    ORDER BY FirmName'

PRINT @sql
EXEC(@sql)
0
 

Author Comment

by:mrosier
ID: 39923905
hello! Thanks for the code Kyle and Scott! My apologies for my absence, I have been out sick a few days. Unfortunately this level of coding is above me, so I would need some time to figure out how to adapt it to my table. But I will gladly share the points between you if you are sure this works out properly so I don't have to leave this question hanging while I do my own legwork?
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 39924476
I'm okay with you leaving it open.  Feel free to post questions as you begin to adapt.
0
 

Author Comment

by:mrosier
ID: 39961566
sorry for the absence, folks, this got resolved via the dynamic solution. A co-worker knew a SQL expert and after showing them this string, the dynamic solution was what they went with, so I will award points to it. Thanks!
0
 

Author Closing Comment

by:mrosier
ID: 39961568
This was adapted to my situation via another SQL user who said this solution was their basis, so the B is because I am trusting that person. Thanks!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now