Solved

Display Multiple Rows in One Line using SQL Query

Posted on 2003-11-10
12
502 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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 wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now