Solved

Display Multiple Rows in One Line using SQL Query

Posted on 2003-11-10
12
504 Views
Last Modified: 2008-03-03
I am using SQL Server 2000 and I have a table in the following form:

PK          Item_ID          Name          Value
1            1000              Price           50
2            1000              Part            Frame
3            1000              Color          Red

I would like to be able to create a SQL Query so that when I search for Item_ID 1000, the information will be displayed similar to the following:

Item_ID        Item Info
1000            Price: 50, Part: Frame, Color: Red


Is this possible without using a Stored Procedure.  Also, if I have to use a view then that wold work for me also.  Thanks.
0
Comment
Question by:pcarrollnf
  • 5
  • 4
  • 3
12 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 9717151
Hi,

Try this:

declare @Item_ID as integer

set @Item_ID = 1000

select Distinct Item_ID
    (
    select 'Price: ' & Value
    from table1
    where Item_ID = @Item_ID
    and Name = 'Price'
    ) as PriceInfo,
    (
    select 'Part: ' & Value
    from table1
    where Item_ID = @Item_ID
    and Name = 'Part'
    ) as PartInfo,
    (
    select 'Color: ' & Value
    from table1
    where Item_ID = @Item_ID
    and Name = 'Color'
    ) as ColorInfo
from table1
where Item_ID = @Item_ID


This should give

1000    Price:50    Part: Frame  Color: Red

With a variation of the above I'm sure you could get the three info fields concatenated into one field.

You may need to cast for the price - I can't tell at this distance but value might be SQL_variant which may require extra work for the price.

Let me know how you go as I'll have more time later today.

Regards
  David
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9717170
It can be done using a cursor or temp table.  

You could also setup 3 views: one for Price only results, one for Part only results and one for Color.  You would then be able to write a quesry based on those 3 views and the original table.

My prefered method would be: stored procedure with 3 temp tables

Leon
0
 
LVL 35

Expert Comment

by:David Todd
ID: 9717370
Hi,

As shown above I beleive it can be done in one query.

But for best performance it should be in a stored procedure that accepts the id as a parameter - If you are only interested in one row at a time.

My above query can do most of what you want and return multiple rows.

If you need a closer answer I need to see table structu7re and some more sample data.

A cursor solution would be __slow__.

Once you get your head around the corelated subquery structure above you wont need three separate views.

I've sued this as a view to present 24 months sales data from a linear table

ie table is
PK
invoicedate
product
debtor
quantity
price
linetotal

and I've gotten it to

product, salesmont1, slaesmonth2, salesmonth3 ...

Regards
  David


Regards
  David
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 29

Expert Comment

by:leonstryker
ID: 9717508
Or you could do this:

SELECT DISTINCT Item_ID, Name INTO #tprice FROM table1 WHERE Name = 'Price'
SELECT DISTINCT Item_ID, Name INTO #tpart FROM table1 WHERE Name = 'Part'
SELECT DISTINCT Item_ID, Name INTO #tcolor FROM table1 WHERE Name = 'Color'

SELECT DISTINCT t.Item_ID, "Price: " + pr.Name + ", Part: " + pa.Name + ", Color: " +c.Name
FROM table1 t, #tprice pr, #tpart pa, #tcolor c
WHERE t.Item_ID = pr.Item_ID
AND t.Item_ID = pa.Item_ID
AND t.Item_ID = c.Item_ID

Leon

0
 
LVL 35

Expert Comment

by:David Todd
ID: 9717633
Hi,

Instead of the temp tables suggested by Leon, is it possible to split the tables along those lines anyway?

That schema change will make this query a lot cleaner, but ...

Regards
  David
0
 

Author Comment

by:pcarrollnf
ID: 9717664
When I run the query from dtodd's proposed answer, I get the following errors:

Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near ')'.

I stripped it down so that the query looks like the following:

declare @Item_ID as integer

set @Item_ID = 1000

select Distinct Item_ID
    (
    select 'Price: ' & Value
    from table1
    where Item_ID = @Item_ID
    and Name = 'Price'
    ) as PriceInfo
from table1
where Item_ID = @Item_ID

Any idea why it is failing?
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9717684
Try using the actual value to see if that works.

Leon
0
 

Author Comment

by:pcarrollnf
ID: 9717743
Just one more thing I wanted to add.  The Name column may have different values in each row also.  For every Item_ID there is not necessarily 3 rows with Price, Part and Frame.  There could be 4 rows for a given Item_ID with the value of Name being Type, Quantity, In Stock, Warehouse just as an example.  The Name column is type varchar, and the Value column is type varchar.  The Item_ID is type int.

I could have, for a given Item_ID, something like the following:

PK          Item_ID          Name          Value
1            1000              Price           50
2            1000              Part            Frame
3            1000              Color          Red
4            1100              Type          Two
5            1100              Quantity      300
6            1100              In Stock      Yes
7            1100              Warehouse  B

So, for Item_ID 1000, I would like to display:

1000            Price: 50, Part: Frame, Color: Red

and for Item_ID 1100:

1000            Type: Two, Quantity: 300, In Stock: Yes, Warehouse: B

I hope this clarifies things.  Thanks.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9717796
pcarrollnf,

In that case I think you will need to use a cursor and a store procedure.  Let me know if you are interested to see the code for it.

Leon
0
 

Author Comment

by:pcarrollnf
ID: 9717806
Alright, I'll have a look at the code for it.  I might be able to use it.  Thanks.
0
 
LVL 29

Assisted Solution

by:leonstryker
leonstryker earned 75 total points
ID: 9718031
CREATE PROC usp_values
AS
DECLARE @vid int
DECLARE @var varchar(255)
DECLARE @vstr varchar(255)

CREATE #ttable1(
Item_ID         int,
Value_Type    varchar(8000)
)

INSERT INTO #ttable
    SELECT DISTINCT Item_ID FROM table1
DECALRE cursor_i CURSOR FOR SELECT Item_ID FROM #ttable
DECLARE cursor1 CURSOR FOR SELECT DISTINCT Name FROM table1

OPEN cursor_i
FETCH NEXT FROM cursor1 INTO @vid
WHILE(@@fetch_status=0)
BEGIN

OPEN cursor1
FETCH NEXT FROM cursor1 INTO @var
WHILE(@@fetch_status=0)
BEGIN
SELECT @vstr = @vstr + (SELECT @var + ': ' + Value FROM table1 WHERE Item_ID = @vid AND Name = @var
FETCH NEXT FROM cursor1 INTO @var
UPDATE #ttable
SET Value_Type = @vstr
WHERE Item_ID = @vid
END
CLOSE cursor1
DEALLOCATE cursor1

FETCH NEXT FROM cursor_i INTO @vid
END
CLOSE cursor_i
DEALLOCATE cursor_i

SELECT * FROM #ttable


This may need some adjustment, but I do not have a SQL Server at work to test this,
Leon

PS I am not promising that this will be fast.  If someone knows a better solution be my guest and post away. :)
0
 
LVL 35

Accepted Solution

by:
David Todd earned 25 total points
ID: 9721020
Hi,

There is a comma missing in my original post. Sorry.

declare @Item_ID as integer

set @Item_ID = 1000

select Distinct Item_ID,
    (
    select 'Price: ' & Value
    from table1
    where Item_ID = @Item_ID
    and Name = 'Price'
    ) as PriceInfo,
    (
    select 'Part: ' & Value
    from table1
    where Item_ID = @Item_ID
    and Name = 'Part'
    ) as PartInfo,
    (
    select 'Color: ' & Value
    from table1
    where Item_ID = @Item_ID
    and Name = 'Color'
    ) as ColorInfo
from table1
where Item_ID = @Item_ID

0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question