Return multiple lines for each record

[Guest]

OK, I'm hoping I'll describe this right, but bear with me.

I have a table of values I need to generate a list from. This list will
have 2 or three lines per record.

As an example, here is the table layout
User, Score, Transaction, StartDate, EndDate, Pct, Comment

I need a query that will output as follows
Line1: Comment1
Line2: Score1, User1, StartDate1, EndDate1, Pct1
Line3: Comment2
Line4: Score2, User2, StartDate2, EndDate2, Pct2

etc. etc.

Basically, a query that will allow me to generate a flat file with 2 lines
per record.

I know this doesn't make sense as to why I would want such a monstrosity,
but it's actually going to generate a long long set of code, the Comment line
being the declaration of what's coming, the next line being the data.

For whatever reason, the system accepting this string won't accept it as a
single line of data.

I first tried making two tables, one having a primary key and comment line,
the 2nd having a primary key and the data line, but I can't figure out how to
put them together in a query to generate this layout.

Any ideas, or should I just go back to Excel? (There will be more than
65,000 lines when all is said and done, so Excel is not the ideal answer).

Thanks for anyone who takes the above challenge!

Rob

 

[Guest]

Here's one way. Build query one where you have and id field and a second
field that concatenates the other fields

SELECT Table1.ID, [Field1] & " " & [Field2] AS Expr1
FROM Table1
ORDER BY Table1.ID;

build query 2 which brings in the id field and the comment field

SELECT Table1.ID, Table1.Coment
FROM Table1;

then performa union of the two queries

select * from query1

UNION select * from query2;

Then you get results like this:
135 753
comment
1444 2544
comment
78956 777
my comments
456 456
say something
445 65488
hello
4445 445687
bonjour


The only drawback is that the fields are concatenated which may be a problem
for you.

You may be able to find a better way if you play around with the code










--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

 

[Guest]

Try this --
SELECT Robert_L_Ross.PrimeID, NULL AS Comment, Robert_L_Ross.User,
Robert_L_Ross.Score, Robert_L_Ross.Transaction, Robert_L_Ross.StartDate,
Robert_L_Ross.EndDate, Robert_L_Ross.Pct, 2 AS X
FROM Robert_L_Ross
UNION SELECT Robert_L_Ross.PrimeID, Robert_L_Ross.Comment, NULL, NULL,
NULL, NULL, NULL, NULL, 1 AS X
FROM Robert_L_Ross
ORDER BY Robert_L_Ross.PrimeID, X;

Substitute your table name.