Solved

Display Multiple Rows in One Line using SQL Query

Posted on 2003-11-10
12
507 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
rolling count by date, hour query 7 30
mssql 7 32
Need help with part of sql query for a condition 12 32
Sql server query 8 20
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

732 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