manipulating SQL table

can you post the schema for your table?

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.

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?

sometimes it is just a pair, sometimes it is upwards above 6 rows.

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             

Select allOpen in new window

ahhh thanks Kyle, do I just put that code all into a new query in SQL Server Management Studio or in some other environment?

SQL server managment studio.  

There are two different approaches.

The first one (https://www.experts-exchange.com/questions/28379230/manipulating-SQL-table.html?anchorAnswerId=39901760#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).

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)

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?

I'm okay with you leaving it open.  Feel free to post questions as you begin to adapt.

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!

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!