Multiple records on single line

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetins all. Suppose I have a table with two columns 'A' and 'B', where A
is a job# and B is a product. Is there a way to query against the table and
show all products in column B on a single line with the job# in column A
listed only once.
I tried selecting job# then doing a series of case statements to make a
column for every product in Column B, but I still ended up with a line for
every entry in Column A. Basically I need this:

JOB1 Product 1 Product 2 Product3 Product4
rather than this:
JOB1 Product 1
JOB1 Product 2
JOB1 Product 3
JOB1 Product 4
Any ideas? Thank you in advance. Access 200, SQL2K
 
Thank you so much Sylvain. I am just beginning to understand all of this, so
much of the information in the article you posted is beyond the scope of what
I have done to date, and it will probably take me days to get one of these
methods to work, so I only want to try one for now. For this reason, and
because by now I trust your judgement very much, do you agree with the
ranking of the methods, and if not which method would you recommend I employ?
 
Sylvain, while reading (and sweating over) the link you posted I came across
an easy solution to what I was trying with the case statements. I had my
syntax wrong, and I needed to put SUM in front of all statements.

select ewo,
sum(case CONVERT(varchar(15),type) + ' ' + CONVERT(varchar(10), size)
when 'Loose Tube 24' then ewo_footage else null end) as [LT 24],
sum(case CONVERT(varchar(15),type) + ' ' + CONVERT(varchar(10), size)
when 'Loose Tube 48' then ewo_footage else null end) as [LT 48],
sum(case CONVERT(varchar(15),type) + ' ' + CONVERT(varchar(10), size)
when 'Loose Tube 72' then ewo_footage else null end) as [LT 72],
from tblcable
group by ewo

The output looks just like a spreadsheet. I just learned about scalability,
and this solution is definately not, but I hope it can help someone else. I
found this solution at http://forums.belution.com/en/sql/000/004/24.shtml
 
Oh, maybe there is a misunderstanding between us. The Case statements (or a
temporary table for more complex situations) in your exemple seems to be
used a part of a Crosstab or Pivot transformation. I did not recognised a
pivot transformation from your first post because you used a single JOB1.

In your exemple, the error that you have made is probably the comma , after
[LT 72] that shouldn't be there (ewo_footage must also be of type numeric).
Also, it's probably better to rewrite your case statement like:

Sum (Case when (Type = 'Loose Tube' and Size = 24) then ewo_footage else
Null End) as [LT 24], ...

Also, using nvarchar (without the parenthesis) instead of varchar(15) with a
concatenation (or +) expression is better and finally, because you are using
Null instead of 0, you will get a Null instead of a 0 as the final result of
the summation if there is no record for this type & size. Maybe this is
what you want but maybe not.

Other references on crosstab and rotation:

http://msdn2.microsoft.com/en-us/library/aa172756(SQL.80).aspx

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q175574

http://www.ftponline.com/archives/premier/mgznarch/vbpj/2001/10oct01/sqlpro0110/rj0110/rj0110-2.asp

http://www.sqlteam.com/item.asp?ItemID=2955

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Greg Snidow said:
Sylvain, while reading (and sweating over) the link you posted I came
across
an easy solution to what I was trying with the case statements. I had my
syntax wrong, and I needed to put SUM in front of all statements.

select ewo,
sum(case CONVERT(varchar(15),type) + ' ' + CONVERT(varchar(10), size)
when 'Loose Tube 24' then ewo_footage else null end) as [LT 24],
sum(case CONVERT(varchar(15),type) + ' ' + CONVERT(varchar(10), size)
when 'Loose Tube 48' then ewo_footage else null end) as [LT 48],
sum(case CONVERT(varchar(15),type) + ' ' + CONVERT(varchar(10), size)
when 'Loose Tube 72' then ewo_footage else null end) as [LT 72],
from tblcable
group by ewo

The output looks just like a spreadsheet. I just learned about
scalability,
and this solution is definately not, but I hope it can help someone else.
I
found this solution at http://forums.belution.com/en/sql/000/004/24.shtml



Sylvain Lafontaine said:
http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/msg/2d85bf366dd9e73e
Click to expand...
Click to expand...
 
Back
Top