Solved

manipulating SQL table

Posted on 2014-03-03
13
382 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 40

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 40

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

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

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 40

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
 

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 40

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:Scott Pletcher
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 40

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

777 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