?
Solved

Display Multiple Rows in One Line using SQL Query

Posted on 2003-11-10
12
Medium Priority
?
534 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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 300 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 100 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

649 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