SQL to make one record appending data of a field in a table

SQL to make one record appending data of a field in a table

Post by Mamu » Sun, 29 Dec 2002 00:49:08



Hi All,

The case is given below:

Microsoft SQL Server  2000 - 8.00.534 (Intel X86)      
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)

I have a table populated with data from a different database (AS400).

This table does not have any keys. It has a field called textline and
lineno along with other fields.
The way table was populated in the old system is given below.

Serial LineNO   TextLine
K29664  1       DESCRIPTION RECORD #1
K29664  2       LIGHTS NOT WORKING
K29664  3      
K29664  4       DESCRIPTION RECORD #2
K29664  5       HARNESS BROKEN AT TOP OF CAB.
K29664  6      
K29664  7       DESCRIPTION RECORD #3  
K29664  8       REMOVED HEADLINER.
K29664  9       WIRING REINSTALLED HEADLINER.  TEST RAN OK
K29665  1       DESCRIPTION RECORD #1  

Similarly the records for other SerialNo were populated. Not all the
serial no has the same number of lineno (it varies from 1 to any
number). Look at the another serial no below.

K29665  1       DESCRIPTION RECORD #1  
K29665  2       HYDROLIC TANK LEAKING  

I do not know why the table was populated in this way. All I know is
that  I will have to get a single record out of this condition with
appending the textline field into one.

My question is how can I write a query so that I can append the
description of textline field into one desctiption for each serialno
field.

I do appreciate your help.
I thank you very much in advance.

Best Regards,
mamun

 
 
 

SQL to make one record appending data of a field in a table

Post by steve dassi » Sun, 29 Dec 2002 04:31:46


Hello,

Conceptually you can first think of pivoting all LineNo's
for each Serial and filling each Serial/LineNo cell
with TextLine.Then you can simply concatenate into a delimited string
the Textline's over all LineNo columns for each row (Serial).You can
also think of forming the string without pivoting,ie. by simply
updating/concatenating the current TextLine value with all the previous
TextLine's for each Serial.
You can, of course, code this in sql(have fun) or in a client app.
You can also use something like the RAC utility running on
the server to easily generate the result.

exec Rac
-- Pivot LineNo for each Serial using the

-- Pivot the LineNo's.It is not necessary to supply

-- Optional using a filter (WHERE clause).


-- Concatenate all the TextLine values for each


-- Omit/cut off the pivot columns (LineNo's) from the result
-- since we are only interested in the concatenated

-- Optionally save the result in a permanent/temporary table

There are many other options to choose from:).


www.rac4sql.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. Appending data from Access table to SQL table

I need to append some data from an Access table to a SQL table and retain
the Access autonumber values. I got the Knowledge Base article Q152035 that
says to run a pass through query (SET IDENTITY_INSERT tablename ON), run
the append query and run another pass through query (SET IDENTITY_INSERT
tablename OFF).

I have SQL Trace running and can see that the SET IDENTITY_INSERT tablename
ON runs. When I run the append query from Access, I get an ODBC error
"Attempting to insert explicit value for identity column in table
'tablename' when IDENTITY_INSERT is set to OFF".

The SQL Trace screen indicates the following lines are run:
SET IDENTITY_INSERT tblPosting ON
go

select substring('NY', status/1024&1+1, 1) from master..sysdatabases where
name=DB_NAME()
go

set implicit_transactions on
go

INSERT INTO "dbo"."tblPostings" ( all the fields) VALUES (first row of
values)
go


go

set implicit_transactions off
go

The "SET IDENTITY_INSERT tblPosting ON
go" line uses SPID=17. All the other lines use SPID=12.

Any help would be appreciated.

--

College of Veterinary Medicine
WASHINGTON STATE UNIVERSITY

2. record sets. cant do multiple searches?

3. Need Help: search/replace data in one field/one record, by queries or script: PDOX5

4. how to insert ??? into tables

5. Appending records from Access 97 tables in SQL tables

6. any way to print index definitions?

7. VB3 appending a field to a table, then entering data into the new field

8. Simple SQL Newbie Question

9. Updating one table with the data from another table with matching field

10. data movement from one table to several tables using a SQL Query

11. combining data into one table from multiple tables - please disregard a previous one, wrong email

12. Fill field in one table from field in another table

13. one table with lots of records or more table with less records