Grouped rows inserted into columns

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

SQL Server 2000 Forums

SQL Server Development (2000)

Grouped rows inserted into columns

Author

Topic

baaul
Starting Member

16 Posts

Posted - 2006-10-24 : 16:35:54

Hi,
I need to INSERT rows into #final table from #tmp with groups of 'grp_code' and 'bap_id' combination rows.

Create Table #tmp (
sort_id int default 0,
grp_Code int default 1,
bap_id INT,
value1_tx varchar(500) default NULL,
value2_tx varchar(500) default NULL
)

Data in the #tmp table:

Sort_id grp_code bap_id Value1_tx Value2_tx

1 1 1 Alabama NULL
1 1 1 Alaska NULL
1 1 1 Arizona NULL
1 1 1 California NULL
2 1 2 Alaska NULL
2 1 2 Arizona NULL
2 1 2 Arkansas NULL
2 1 2 California NULL
3 1 3 Alaska NULL
3 1 3 Arizona NULL
3 1 3 Arkansas NULL
1 2 1 ACS Image Solutions NULL
1 2 1 ABC Virtual Communications, Inc NULL
1 2 1 ACS Unclaimed Propery Clearinghouse, Inc. NULL
1 2 1 ABC Virtual Communications, Inc NULL
1 2 1 ABC Virtual Communications, Inc NULL
1 2 1 Acxiom NULL
1 2 1 ABC Virtual Communications, Inc NULL
2 2 2 ABC Virtual Communications, Inc NULL
2 2 2 ACS Unclaimed Propery Clearinghouse, Inc. NULL
2 2 2 #NAME? NULL
2 2 2 ACS Image Solutions NULL
2 2 2 Acxiom NULL
2 2 2 ADP NULL
2 2 2 ADP Investor Communication NULL
2 2 2 Bancomer Transfer Services NULL
3 2 3 ABC Virtual Communications, Inc NULL
3 2 3 ACS Image Solutions NULL
3 2 3 ACS Unclaimed Propery Clearinghouse, Inc. NULL
3 2 3 Acxiom NULL
3 2 3 ABC Virtual Communications, Inc NULL
3 2 3 ACS Image Solutions NULL
1 3 1 STATE AND LOCAL LAWS AND REGULATIONS Insurance Laws & Regulations - Various States
1 3 1 STATE AND LOCAL LAWS AND REGULATIONS Escheat Laws
1 3 1 STATE AND LOCAL LAWS AND REGULATIONS State Credit Law Requirements
1 3 1 STATE AND LOCAL LAWS AND REGULATIONS State Deceptive Practices Laws
1 3 1 OTHER FEDERAL LAWS AND REGULATIONS American's with Disability Act Public Law 101-336 - Abstract
2 3 2 OTHER FEDERAL LAWS AND REGULATIONS Bankruptcy Laws
2 3 2 OTHER FEDERAL LAWS AND REGULATIONS COBRA
2 3 2 OTHER FEDERAL LAWS AND REGULATIONS Department of Labor Rules

Create Table #final (
sort_id INT,
bap_id INT,
geography varchar(120) NULL,
vendor_nm varchar(250) NULL,
regulator varchar(100) NULL,
regulation varchar(500) NULL
)

This is how it needs to be inserted into #final table:

rows from value1_tx inserted into Column 'geography' WHERE grp_code =1 and bap_id = 1
rows from value1_tx inserted into Column 'vendor_nm' WHERE grp_code =2 and bap_id = 1
rows from value1_tx inserted into Column 'regulator'WHERE grp_code =3 and bap_id = 1
rows from value2_tx inserted into Column 'regulation' WHERE grp_code =3 and bap_id = 1

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-24 : 17:33:35

[code]INSERT #final
SELECT sort_id, bap_id, value1_tx, NULL, NULL, NULL
FROM #tmp
WHERE grp_code =1 and bap_id = 1

INSERT #final
SELECT sort_id, bap_id, NULL, value1_tx, NULL, NULL
FROM #tmp
WHERE grp_code =2 and bap_id = 1[/code]

Not sure for the last one if you want separate rows or single rows
These two for separate
[code]INSERT #final
SELECT sort_id, bap_id, NULL, NULL, value1_tx, NULL
FROM #tmp
WHERE grp_code =3 and bap_id = 1

INSERT #final
SELECT sort_id, bap_id, NULL, NULL, NULL, value2_tx
FROM #tmp
WHERE grp_code =3 and bap_id = 1[/code]

This one for single
[code]INSERT #final
SELECT sort_id, bap_id, NULL, NULL, value1_tx, value2_tx
FROM #tmp
WHERE grp_code =3 and bap_id = 1[/code]

baaul
Starting Member

16 Posts

Posted - 2006-10-24 : 18:30:06

Thanks for replying so quickly. Currently, after the Insert I'm getting the data in #final table as:

Sort_id bap_id Vendor_nm Vendor_nm Regulator Regulation

1 1 Alabama NULL NULL NULL
1 1 Alaska NULL NULL NULL
1 1 Arizona NULL NULL NULL
1 1 California NULL NULL NULL
1 1 NULL ACS Image Solutions NULL NULL
1 1 NULL ABC Virtual Communications, Inc NULL NULL
1 1 NULL ACS Unclaimed Propery Clearinghouse, Inc. NULL NULL
1 1 NULL ABC Virtual Communications, Inc NULL NULL
1 1 NULL ABC Virtual Communications, Inc NULL NULL
1 1 NULL Acxiom NULL NULL
1 1 NULL ABC Virtual Communications, Inc NULL NULL
1 1 NULL NULL FEDERAL RESERVE REGULATIONS Reg. AA - 12 CFR 227 - Unfair or Deceptive Acts
1 1 NULL NULL FEDERAL RESERVE REGULATIONS Reg. B - 12 CFR 202 - Equal Credit Opportunity
1 1 NULL NULL FEDERAL RESERVE REGULATIONS Reg. B - 12 CFR 202 - Equal Credit Opportunity
===================================================

However, I'd like the 'vendor_nm', 'regulator' and 'regulation' groups moved up to the top. Below is the desired result.
----------------------------------------------------------

Sort_id bap_id Vendor_nm Vendor_nm Regulator Regulation

1 1 Alabama ACS Image Solutions FEDERAL RESERVE REGULATIONS Reg. AA - 12 CFR 227 - Unfair or Deceptive Acts
1 1 Alaska ABC Virtual Communications, Inc FEDERAL RESERVE REGULATIONS Reg. B - 12 CFR 202 - Equal Credit Opportunity
1 1 Arizona ACS Unclaimed Propery Clearinghouse, Inc. FEDERAL RESERVE REGULATIONS Reg. B - 12 CFR 202 - Equal Credit Opportunity
1 1 California ABC Virtual Communications, Inc NULL NULL
1 1 NULL ABC Virtual Communications, Inc NULL NULL
1 1 NULL Acxiom NULL NULL
1 1 NULL ABC Virtual Communications, Inc NULL NULL
1 1 NULL NULL NULL NULL
1 1 NULL NULL NULL NULL
1 1 NULL NULL NULL NULL
1 1 NULL NULL NULL NULL
1 1 NULL NULL NULL NULL
1 1 NULL NULL NULL NULL
1 1 NULL NULL NULL NULL

baaul
Starting Member

16 Posts

Posted - 2006-10-24 : 19:25:49

More info: I may have more than ONE bap_id(variable)... could be 50 bap_ids and 4 grp_code(constant).
=========================================
Dataset I'm getting after running the code below for 2 bap_ids:

INSERT #final
SELECT sort_id, bap_id,null, value1_tx, NULL, NULL, NULL
FROM #tmp
WHERE grp_code =1 and bap_id = 1

INSERT #final
SELECT sort_id, bap_id,null,null, value1_tx, NULL, NULL
FROM #tmp
WHERE grp_code =2 and bap_id = 1

INSERT #final
SELECT sort_id,bap_id, NULL, NULL, NULL, value1_tx, value2_tx
FROM #tmp
WHERE grp_code =3 and bap_id = 1

INSERT #final
SELECT sort_id, bap_id,null, value1_tx, NULL, NULL, NULL
FROM #tmp
WHERE grp_code =1 and bap_id = 2

INSERT #final
SELECT sort_id, bap_id,null,null, value1_tx, NULL, NULL
FROM #tmp
WHERE grp_code =2 and bap_id = 2

INSERT #final
SELECT sort_id,bap_id, NULL, NULL, NULL, value1_tx, value2_tx
FROM #tmp
WHERE grp_code =3 and bap_id = 2

Sort_id bap_id Geography Vendor_nm Regulator Regulation

1 1 Alabama NULL NULL NULL
1 1 Alaska NULL NULL NULL
1 1 Arizona NULL NULL NULL
1 1 California NULL NULL NULL
1 1 NULL ACS Image Solutions NULL NULL
1 1 NULL ABC Virtual Communications, Inc NULL NULL
1 1 NULL ACS Unclaimed Propery Clearinghouse, Inc. NULL NULL
1 1 NULL ABC Virtual Communications, Inc NULL NULL
1 1 NULL ABC Virtual Communications, Inc NULL NULL
1 1 NULL Acxiom NULL NULL
1 1 NULL ABC Virtual Communications, Inc NULL NULL
1 1 NULL NULL FEDERAL RESERVE REGULATIONS Reg. AA - 12 CFR 227 - Unfair or Deceptive Acts
1 1 NULL NULL FEDERAL RESERVE REGULATIONS Reg. B - 12 CFR 202 - Equal Credit Opportunity
1 1 NULL NULL FEDERAL RESERVE REGULATIONS Reg. B - 12 CFR 202 - Equal Credit Opportunity
1 1 NULL NULL FEDERAL RESERVE REGULATIONS Reg. C - 12 CFR 203 - Home Mortgage Disclosure
1 1 NULL NULL FEDERAL RESERVE REGULATIONS Reg. CC - 12 CFR 229 - Availability of Funds & Collection of Checks
1 1 NULL NULL FEDERAL RESERVE REGULATIONS Reg. D - 12 CFR 204 - Reserve Requirements of Depository Institutions
2 2 Alaska NULL NULL NULL
2 2 Arizona NULL NULL NULL
2 2 Arkansas NULL NULL NULL
2 2 California NULL NULL NULL
2 2 NULL ABC Virtual Communications, Inc NULL NULL
2 2 NULL ACS Unclaimed Propery Clearinghouse, Inc. NULL NULL
2 2 NULL #NAME? NULL NULL
2 2 NULL ACS Image Solutions NULL NULL
2 2 NULL Acxiom NULL NULL
2 2 NULL ADP NULL NULL
2 2 NULL ADP Investor Communication NULL NULL
2 2 NULL Bancomer Transfer Services NULL NULL
2 2 NULL Bank of New York NULL NULL
2 2 NULL First American Commercial Real Estate Tax Services Inc. NULL NULL
2 2 NULL First American Default Management Solutions NULL NULL
2 2 NULL First American Default Technologies NULL NULL
2 2 NULL First American eAppraiseIT NULL NULL
2 2 NULL First American Field Services NULL NULL
2 2 NULL First American Flood Data Services (FAFDS) NULL NULL
2 2 NULL First American National Default Title Services NULL NULL
2 2 NULL First American Nationwide Documents (FAND) NULL NULL
2 2 NULL First American Real Estate Solutions (FARES) NULL NULL
2 2 NULL First American Real Estate Tax Services NULL NULL
2 2 NULL First American Title-Lenders Advantage NULL NULL
2 2 NULL First Canadian Title NULL NULL
2 2 NULL First Data Corp NULL NULL
2 2 NULL ABC Virtual Communications, Inc NULL NULL
2 2 NULL ACS Image Solutions NULL NULL
2 2 NULL ACS Unclaimed Propery Clearinghouse, Inc. NULL NULL
2 2 NULL Platinum Direct NULL NULL
2 2 NULL POS Portal NULL NULL
2 2 NULL NULL FEDERAL RESERVE REGULATIONS Reg S Subpart B - 12 CFR 219 - Record Keeping Requirements for Certain Financial Records
2 2 NULL NULL FDIC REGULATIONS 12 CFR 330 FDIC Deposit Insurance
2 2 NULL NULL STATE AND LOCAL LAWS AND REGULATIONS UCC - Uniform Commercial Code

============================================================

MY DESIRED DATASET should look like:------>

Sort_id bap_id Geography Vendor_nm Regulator Regulation

1 1 Alabama ACS Image Solutions FEDERAL RESERVE REGULATIONS Reg. AA - 12 CFR 227 - Unfair or Deceptive Acts
1 1 Alaska ABC Virtual Communications, Inc FEDERAL RESERVE REGULATIONS Reg. B - 12 CFR 202 - Equal Credit Opportunity
1 1 Arizona ACS Unclaimed Propery Clearinghouse, Inc. FEDERAL RESERVE REGULATIONS Reg. B - 12 CFR 202 - Equal Credit Opportunity
1 1 California ABC Virtual Communications, Inc FEDERAL RESERVE REGULATIONS Reg. C - 12 CFR 203 - Home Mortgage Disclosure
1 1 NULL ABC Virtual Communications, Inc FEDERAL RESERVE REGULATIONS Reg. CC - 12 CFR 229 - Availability of Funds & Collection of Checks
1 1 NULL Acxiom FEDERAL RESERVE REGULATIONS Reg. D - 12 CFR 204 - Reserve Requirements of Depository Institutions
1 1 NULL ABC Virtual Communications, Inc NULL NULL
1 1 NULL NULL NULL NULL
1 1 NULL NULL NULL NULL
1 1 NULL NULL NULL NULL
1 1 NULL NULL NULL NULL
1 1 NULL NULL NULL NULL
1 1 NULL NULL NULL NULL
1 1 NULL NULL NULL NULL
1 1 NULL NULL NULL NULL
1 1 NULL NULL NULL NULL
1 1 NULL NULL NULL NULL
2 2 Alaska ABC Virtual Communications, Inc FEDERAL RESERVE REGULATIONS Reg S Subpart B - 12 CFR 219 - Record Keeping Requirements for Certain Financial Records
2 2 Arizona ACS Unclaimed Propery Clearinghouse, Inc. FDIC REGULATIONS 12 CFR 330 FDIC Deposit Insurance
2 2 Arkansas #NAME? STATE AND LOCAL LAWS AND REGULATIONS UCC - Uniform Commercial Code
2 2 California ACS Image Solutions NULL NULL
2 2 NULL Acxiom NULL NULL
2 2 NULL ADP NULL NULL
2 2 NULL ADP Investor Communication NULL NULL
2 2 NULL Bancomer Transfer Services NULL NULL
2 2 NULL Bank of New York NULL NULL
2 2 NULL First American Commercial Real Estate Tax Services Inc. NULL NULL
2 2 NULL First American Default Management Solutions NULL NULL
2 2 NULL First American Default Technologies NULL NULL
2 2 NULL First American eAppraiseIT NULL NULL
2 2 NULL First American Field Services NULL NULL
2 2 NULL First American Flood Data Services (FAFDS) NULL NULL
2 2 NULL First American National Default Title Services NULL NULL
2 2 NULL First American Nationwide Documents (FAND) NULL NULL
2 2 NULL First American Real Estate Solutions (FARES) NULL NULL
2 2 NULL First American Real Estate Tax Services NULL NULL
2 2 NULL First American Title-Lenders Advantage NULL NULL
2 2 NULL First Canadian Title NULL NULL
2 2 NULL First Data Corp NULL NULL
2 2 NULL ABC Virtual Communications, Inc NULL NULL
2 2 NULL ACS Image Solutions NULL NULL
2 2 NULL ACS Unclaimed Propery Clearinghouse, Inc. NULL NULL
2 2 NULL Platinum Direct NULL NULL
2 2 NULL POS Portal NULL NULL
2 2 NULL NULL NULL NULL
2 2 NULL NULL NULL NULL
2 2 NULL NULL NULL NULL
2 2 NULL NULL NULL NULL
2 2 NULL NULL NULL NULL
2 2 NULL NULL NULL NULL
2 2 NULL NULL NULL NULL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-24 : 19:26:32

How do you associate 'Alabama' with 'ACS Image Solutions' and not 'ABC Virtual Communications, Inc' ?

baaul
Starting Member

16 Posts

Posted - 2006-10-24 : 23:59:05

The group data is SORTED in the #tmp table. So, all i need is to INSERT the grouped rows into #final table in that order.

Is that possibile? Thanks in advance.

-baaul

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-25 : 08:48:12

order means nothing in the database. you order it when you retrieve it. You do NOT insert by order, you insert by relation!

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources