query to split a database column ?

How can i write a query to split a database column and shows 2 new columns. In my database column

I have 2 mixing items and need to split out to 2 columns. Normally I have to write a query and change parameter

and run another query.

For example a database column with average number and range number.

Thanks

Daniel

Can you post some DDL, sample data and expected results?

AMB

|||

Hai,

Can you try the below query, and let me know that, it relates to your requirement or not:

DECLARE @.Columns varchar(1000)

SET @.Columns = ''

-- Create a temporary table.

CREATE TABLE #TempTable(Items varchar(50))

INSERT INTO #TempTable(Items) VALUES('A')

INSERT INTO #TempTable(Items) VALUES('A')

INSERT INTO #TempTable(Items) VALUES('A')

INSERT INTO #TempTable(Items) VALUES('B')

INSERT INTO #TempTable(Items) VALUES('B')

INSERT INTO #TempTable(Items) VALUES('B')

INSERT INTO #TempTable(Items) VALUES('C')

INSERT INTO #TempTable(Items) VALUES('C')

INSERT INTO #TempTable(Items) VALUES('D')

INSERT INTO #TempTable(Items) VALUES('D')

-- Before

SELECT * FROM #TempTable

-- Make a column list

SELECT

@.Columns = @.Columns + '[' + Items + '], '

FROM #TempTable

GROUP BY Items

-- Check the column values exits or not.

IF ( @.Columns IS NOT NULL ) AND ( @.Columns <> '' )

BEGIN

DECLARE @.Query nvarchar(1000)

SELECT @.Columns = SUBSTRING(@.Columns,1, LEN(@.Columns)-1)

SELECT @.Query = '

SELECT

*

FROM

(

SELECT

Items

FROM #TempTable

) AS Dummy

PIVOT

(

MAX(Items)

FOR Items IN (' + @.Columns + ')

)AS PvtTable'

EXEC(@.Query)

END

-- Drop the temporary table.

DROP TABLE #TempTable

Please clarify If I did any wrong.

Regards,

Kiran.Y

|||

Perhaps something like:

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
MyGroup int,
MyValue decimal(10,2)
)

INSERT INTO @.MyTable VALUES ( 1, 25 )
INSERT INTO @.MyTable VALUES ( 2, 5 )
INSERT INTO @.MyTable VALUES ( 1, 10 )
INSERT INTO @.MyTable VALUES ( 1, 15 )
INSERT INTO @.MyTable VALUES ( 1, 4 )
INSERT INTO @.MyTable VALUES ( 2, 6 )
INSERT INTO @.MyTable VALUES ( 2, 11 )
INSERT INTO @.MyTable VALUES ( 2, 0 )
INSERT INTO @.MyTable VALUES ( 1, 12 )

SELECT
Average = cast( avg( MyValue ) AS decimal(10,2)),
Range = ( cast( min( MyValue ) AS varchar(10)) + '-' +
cast( max( MyValue ) AS varchar(10)))
FROM @.MyTable
GROUP BY MyGroup

Average Range
13.20 4.00-25.00
5.50 0.00-11.00

|||

Hi Kiran

Thanks for answering my email. To clarify this below are my tables and columns and my query

Table: Item Stat_label Stat_value

column: Pack ID Stat_label_ID Stat_value_ID

Pack_Num Label ( has 2 rows Value

Ave and Range)

My query to list Pack_Num, Ave and it's value

SELECT Item.Pack_Num, Stat_label.Label, Stat_value.Value

FROM Item, Stat_label, Stat_value

WHERE Item.packID=Stat_label.Stat_label_ID AND

Stat_label.Stat_lavel_ID=Stat_value.Stat_value_ID

AND Stat_label.Label= Ave

My question: I want a query to list Pack_Num, Ave, Range and value

How can I do it?

That's mean this query need to split the Stat_label and list another

column name"Range".

Thanks
Daniel

|||

If you are using SQL 2005, look into the PIVOT function.

If you are using SQL 2000, explore using CASE.

Maybe these articles will help:

Pivot Tables -A simple way to perform crosstab operations
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1131829,00.html

Pivot Tables - How to rotate a table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;175574

Pivot Tables -Dynamic Cross-Tabs
http://www.sqlteam.com/item.asp?ItemID=2955

Pivot Tables - Crosstab Pivot-table Workbench
http://www.simple-talk.com/sql/t-sql-programming/crosstab-pivot-table-workbench/

|||

Thanks all

I can not use "insert" because my account for this is read only and I avoid to list everything in a column and and use Excel pivot to summary.

Daniel

|||

Daniel,

If you would carefully examine the code provided, you will see that the INSERT statements are only building a sample table so that we could demonstrate a query suggestion.

You didn't bother to provide the table DDL, or sample data, so we have to waste our time creating sample data for you. and apparently, you can't read and understand example code.

|||

This may be closer to what you are hoping to find:

SELECT

i.Pack_Num,

sl.Stat_Label,
Average = cast( avg( sv.Stat_Value ) AS decimal(10,2)),
Range = ( cast( min( sv.Stat_Value ) AS varchar(10)) + '-' +
cast( max( sv.Stat_Value ) AS varchar(10)))
FROM Item i

JOIN Stat_Label sl

ON i.Pack_ID = sl.Stat_Label_ID

JOIN Stat_Value sv

ON sl.Stat_Label_ID = sv.Stat_Value_ID

WHERE sl.Label = 'Ave'
GROUP BY

i.Pack_Num,

sl.Stat_Label

|||

Thanks Anrnie but It is not working

Error at Average= cast......

Error at Range= (cast......

My Average and Range are decimal, no need cast

Do I have to declare a temp table?

Daniel

|||

Actually, it appears that the Stat_Value is most most likely a varchar().

Before we can help you any further, please post the table DDL and some sample data in the form of INSERT statements. Please refer to this link for help in preparing your material.

|||

Can SQL query create a new column or not?. DO NOT want to make a temp table.

Thanks

Daniel

|||

Can TSQL create a new column at the output?

If not I need 2 select statement but how to joint them? Can not use EXCEPT in TSQL? Tried to use UNION but

the results in one column.

It's complicated with creating a temp table since I do not know how to insert to temp table from database.

Thanks

Daniel

|||Please supply the requested information. (See my previous post.)

sql

Query to return count of items missing every hour

Hello,
I have been trying to work on a query to return the amount of entries
that are not in each hour. There is a problem with the syncronisation
between our databases and I want to find a pattern, in the hours or in
the tags on what is not syncronising. In one DB (primary) I have the
full 6000 records, per hour, over the span of a weekend the backup
site was short about 200 records.
An example of the tables is
TableA TableB
tagId tagName tagId
gmt_time(hourly)
PK composite PK
approx 6000 records approx 6000 every hour
So essentially I would like to see if anyone would know a query that
would group by the hours and return which tags were not present in
that hour.
Thank you in advance for any help.
Andy McDonagh
select tagid, datepart(d, gmt_time) as dy, datepart(hh, gmt_time) as hr
from tablea a (nolock)
where not exists (select * from tableb b (nolock) where a.tagid = b.tagid)
group by datepart(d, gmt_time), datepart(hh, gmt_time)
TheSQLGuru
President
Indicium Resources, Inc.
<mcdonaghandy@.gmail.com> wrote in message
news:1184775962.160779.8450@.x35g2000prf.googlegrou ps.com...
> Hello,
> I have been trying to work on a query to return the amount of entries
> that are not in each hour. There is a problem with the syncronisation
> between our databases and I want to find a pattern, in the hours or in
> the tags on what is not syncronising. In one DB (primary) I have the
> full 6000 records, per hour, over the span of a weekend the backup
> site was short about 200 records.
> An example of the tables is
> TableA TableB
> tagId tagName tagId
> gmt_time(hourly)
> PK composite PK
> approx 6000 records approx 6000 every hour
> So essentially I would like to see if anyone would know a query that
> would group by the hours and return which tags were not present in
> that hour.
> Thank you in advance for any help.
> Andy McDonagh
>

Query to return count of items missing every hour

Hello,
I have been trying to work on a query to return the amount of entries
that are not in each hour. There is a problem with the syncronisation
between our databases and I want to find a pattern, in the hours or in
the tags on what is not syncronising. In one DB (primary) I have the
full 6000 records, per hour, over the span of a weekend the backup
site was short about 200 records.
An example of the tables is
TableA TableB
tagId tagName tagId
gmt_time(hourly)
PK composite PK
approx 6000 records approx 6000 every hour
So essentially I would like to see if anyone would know a query that
would group by the hours and return which tags were not present in
that hour.
Thank you in advance for any help.
Andy McDonaghselect tagid, datepart(d, gmt_time) as dy, datepart(hh, gmt_time) as hr
from tablea a (nolock)
where not exists (select * from tableb b (nolock) where a.tagid = b.tagid)
group by datepart(d, gmt_time), datepart(hh, gmt_time)
TheSQLGuru
President
Indicium Resources, Inc.
<mcdonaghandy@.gmail.com> wrote in message
news:1184775962.160779.8450@.x35g2000prf.googlegroups.com...
> Hello,
> I have been trying to work on a query to return the amount of entries
> that are not in each hour. There is a problem with the syncronisation
> between our databases and I want to find a pattern, in the hours or in
> the tags on what is not syncronising. In one DB (primary) I have the
> full 6000 records, per hour, over the span of a weekend the backup
> site was short about 200 records.
> An example of the tables is
> TableA TableB
> tagId tagName tagId
> gmt_time(hourly)
> PK composite PK
> approx 6000 records approx 6000 every hour
> So essentially I would like to see if anyone would know a query that
> would group by the hours and return which tags were not present in
> that hour.
> Thank you in advance for any help.
> Andy McDonagh
>

Query to return count of items missing every hour

Hello,
I have been trying to work on a query to return the amount of entries
that are not in each hour. There is a problem with the syncronisation
between our databases and I want to find a pattern, in the hours or in
the tags on what is not syncronising. In one DB (primary) I have the
full 6000 records, per hour, over the span of a weekend the backup
site was short about 200 records.
An example of the tables is
TableA TableB
tagId tagName tagId
gmt_time(hourly)
PK composite PK
approx 6000 records approx 6000 every hour
So essentially I would like to see if anyone would know a query that
would group by the hours and return which tags were not present in
that hour.
Thank you in advance for any help.
Andy McDonaghselect tagid, datepart(d, gmt_time) as dy, datepart(hh, gmt_time) as hr
from tablea a (nolock)
where not exists (select * from tableb b (nolock) where a.tagid = b.tagid)
group by datepart(d, gmt_time), datepart(hh, gmt_time)
TheSQLGuru
President
Indicium Resources, Inc.
<mcdonaghandy@.gmail.com> wrote in message
news:1184775962.160779.8450@.x35g2000prf.googlegroups.com...
> Hello,
> I have been trying to work on a query to return the amount of entries
> that are not in each hour. There is a problem with the syncronisation
> between our databases and I want to find a pattern, in the hours or in
> the tags on what is not syncronising. In one DB (primary) I have the
> full 6000 records, per hour, over the span of a weekend the backup
> site was short about 200 records.
> An example of the tables is
> TableA TableB
> tagId tagName tagId
> gmt_time(hourly)
> PK composite PK
> approx 6000 records approx 6000 every hour
> So essentially I would like to see if anyone would know a query that
> would group by the hours and return which tags were not present in
> that hour.
> Thank you in advance for any help.
> Andy McDonagh
>

Query to group sequential items

Let's say I have the following table:

entry product quality
1 A 80
2 A 70
3 A 80
4 B 60
5 B 90
6 C 80
7 D 80
8 A 50
9 C 70

I'm looking for a way to find the average "quality" value for a
SEQUENTIAL GROUPING of the same Product. For exmple, I need an
average of Entry 1+2+3 (because this is the first grouping of the same
product type), but NOT want that average to include row 8 (which is
also Product A, but in a different "group".)

I'm sure it can be done (because I can describe it!), but I'll be a
monkey's uncle if I can figure out how. I would imagine it would
involve some sort of running tally that references the next record as
it goes... to see if the product type has changed. Perhaps use of a
temporary table?

Muchas gracias!!
Cy.Easy way ... cursor or loop thru as you stated.

WARNING - THE FOLLOWING IS AN UNTESTED HALF BACKED IDEA -
CONSUME AT YOUR OWN RISK

The set oriented way would require the addition of a grouping column,
initially null and populated via update statements from a temp table

use something like this to generate a set of the grouping transition rows.

-- GENERATED GROUP IDS AND GET MAX ENTRY IN GROUP
select
identity(int,1,1) as groupid
a.product,
a.entry
into #groupings
from mytable a
join mytable b on a.product != b.product and a.entry = b.entry + 1

-- UPDATES BASE TABLE WITH FOR MAX ENTRY IN GROUP
update a
set groupid = g.groupid
from mytable a
join #grouping g on a.entry = g.entry

-- UPDATES PRIOR ENTRIES IN GROUP
update a
set a.groupid = g.groupid
from mytable a
join #grouping g on a.entry < g.entry
where a.groupid is null

-- QUERY TO RETURN RESULTS YOU ARE LOOKING FOR
select groupid , min( product ) , max( entry ) , min( entry) , sum (
quantity ) , count(*) , avg( quantity)
from mytable
group by groupid

----

I am not so sure about the 2nd update here, as I am tired and going to bed
soon. you may also need to join to the grouping temp table on the product
and also put a not exists() in the where clause, but you may be covered by
the simple is null to prevent muliple updates.

Let me know how you make out, and if this points you in a good direction or
throws you off track.

<cyrus.kapadia@.us.pm.com> wrote in message
news:1102562637.046747.292110@.c13g2000cwb.googlegr oups.com...
> Let's say I have the following table:
> entry product quality
> 1 A 80
> 2 A 70
> 3 A 80
> 4 B 60
> 5 B 90
> 6 C 80
> 7 D 80
> 8 A 50
> 9 C 70
> I'm looking for a way to find the average "quality" value for a
> SEQUENTIAL GROUPING of the same Product. For exmple, I need an
> average of Entry 1+2+3 (because this is the first grouping of the same
> product type), but NOT want that average to include row 8 (which is
> also Product A, but in a different "group".)
> I'm sure it can be done (because I can describe it!), but I'll be a
> monkey's uncle if I can figure out how. I would imagine it would
> involve some sort of running tally that references the next record as
> it goes... to see if the product type has changed. Perhaps use of a
> temporary table?
> Muchas gracias!!
> Cy.|||<cyrus.kapadia@.us.pm.com> wrote in message
news:1102562637.046747.292110@.c13g2000cwb.googlegr oups.com...
> Let's say I have the following table:
> entry product quality
> 1 A 80
> 2 A 70
> 3 A 80
> 4 B 60
> 5 B 90
> 6 C 80
> 7 D 80
> 8 A 50
> 9 C 70
> I'm looking for a way to find the average "quality" value for a
> SEQUENTIAL GROUPING of the same Product. For exmple, I need an
> average of Entry 1+2+3 (because this is the first grouping of the same
> product type), but NOT want that average to include row 8 (which is
> also Product A, but in a different "group".)
> I'm sure it can be done (because I can describe it!), but I'll be a
> monkey's uncle if I can figure out how. I would imagine it would
> involve some sort of running tally that references the next record as
> it goes... to see if the product type has changed. Perhaps use of a
> temporary table?
> Muchas gracias!!
> Cy.

CREATE TABLE ProductEntries
(
product_entry INT NOT NULL PRIMARY KEY,
product_code CHAR(1) NOT NULL,
product_quality INT NOT NULL
)

INSERT INTO ProductEntries (product_entry, product_code, product_quality)
VALUES (1, 'A', 80)
INSERT INTO ProductEntries (product_entry, product_code, product_quality)
VALUES (2, 'A', 70)
INSERT INTO ProductEntries (product_entry, product_code, product_quality)
VALUES (3, 'A', 80)
INSERT INTO ProductEntries (product_entry, product_code, product_quality)
VALUES (4, 'B', 60)
INSERT INTO ProductEntries (product_entry, product_code, product_quality)
VALUES (5, 'B', 90)
INSERT INTO ProductEntries (product_entry, product_code, product_quality)
VALUES (6, 'C', 80)
INSERT INTO ProductEntries (product_entry, product_code, product_quality)
VALUES (7, 'D', 80)
INSERT INTO ProductEntries (product_entry, product_code, product_quality)
VALUES (8, 'A', 50)
INSERT INTO ProductEntries (product_entry, product_code, product_quality)
VALUES (9, 'C', 70)

SELECT PR.product_code AS product_code,
PR.start_product_entry AS start_product_entry,
MAX(P.product_entry) AS end_product_entry,
AVG(CAST(P.product_quality AS DECIMAL)) AS avg_product_quality
FROM (SELECT MIN(PE.product_entry) AS start_product_entry,
PE.next_product_entry AS end_product_entry,
PE.product_code
FROM (SELECT P1.product_entry, P1.product_code,
MIN(P2.product_entry) AS next_product_entry
FROM ProductEntries AS P1
LEFT OUTER JOIN
ProductEntries AS P2
ON P2.product_entry > P1.product_entry AND
P2.product_code <> P1.product_code
GROUP BY P1.product_entry, P1.product_code) AS PE
GROUP BY PE.product_code, PE.next_product_entry) AS PR
INNER JOIN
ProductEntries AS P
ON P.product_code = PR.product_code AND
P.product_entry >= PR.start_product_entry AND
(PR.end_product_entry IS NULL OR
P.product_entry < PR.end_product_entry)
GROUP BY PR.product_code, PR.start_product_entry
ORDER BY start_product_entry

product_code start_product_entry end_product_entry avg_product_quality
A 1 3 76.666666
B 4 5 75.000000
C 6 6 80.000000
D 7 7 80.000000
A 8 8 50.000000
C 9 9 70.000000

--
JAG|||Sure, that may work as well.

"John Gilson" <jag@.acm.org> wrote in message
news:5zQtd.72060$Vk6.20781@.twister.nyc.rr.com...
> <cyrus.kapadia@.us.pm.com> wrote in message
> news:1102562637.046747.292110@.c13g2000cwb.googlegr oups.com...
>> Let's say I have the following table:
>>
>> entry product quality
>> 1 A 80
>> 2 A 70
>> 3 A 80
>> 4 B 60
>> 5 B 90
>> 6 C 80
>> 7 D 80
>> 8 A 50
>> 9 C 70
>>
>> I'm looking for a way to find the average "quality" value for a
>> SEQUENTIAL GROUPING of the same Product. For exmple, I need an
>> average of Entry 1+2+3 (because this is the first grouping of the same
>> product type), but NOT want that average to include row 8 (which is
>> also Product A, but in a different "group".)
>>
>> I'm sure it can be done (because I can describe it!), but I'll be a
>> monkey's uncle if I can figure out how. I would imagine it would
>> involve some sort of running tally that references the next record as
>> it goes... to see if the product type has changed. Perhaps use of a
>> temporary table?
>>
>> Muchas gracias!!
>> Cy.
> CREATE TABLE ProductEntries
> (
> product_entry INT NOT NULL PRIMARY KEY,
> product_code CHAR(1) NOT NULL,
> product_quality INT NOT NULL
> )
> INSERT INTO ProductEntries (product_entry, product_code, product_quality)
> VALUES (1, 'A', 80)
> INSERT INTO ProductEntries (product_entry, product_code, product_quality)
> VALUES (2, 'A', 70)
> INSERT INTO ProductEntries (product_entry, product_code, product_quality)
> VALUES (3, 'A', 80)
> INSERT INTO ProductEntries (product_entry, product_code, product_quality)
> VALUES (4, 'B', 60)
> INSERT INTO ProductEntries (product_entry, product_code, product_quality)
> VALUES (5, 'B', 90)
> INSERT INTO ProductEntries (product_entry, product_code, product_quality)
> VALUES (6, 'C', 80)
> INSERT INTO ProductEntries (product_entry, product_code, product_quality)
> VALUES (7, 'D', 80)
> INSERT INTO ProductEntries (product_entry, product_code, product_quality)
> VALUES (8, 'A', 50)
> INSERT INTO ProductEntries (product_entry, product_code, product_quality)
> VALUES (9, 'C', 70)
> SELECT PR.product_code AS product_code,
> PR.start_product_entry AS start_product_entry,
> MAX(P.product_entry) AS end_product_entry,
> AVG(CAST(P.product_quality AS DECIMAL)) AS
> avg_product_quality
> FROM (SELECT MIN(PE.product_entry) AS start_product_entry,
> PE.next_product_entry AS end_product_entry,
> PE.product_code
> FROM (SELECT P1.product_entry, P1.product_code,
> MIN(P2.product_entry) AS
> next_product_entry
> FROM ProductEntries AS P1
> LEFT OUTER JOIN
> ProductEntries AS P2
> ON P2.product_entry >
> P1.product_entry AND
> P2.product_code <>
> P1.product_code
> GROUP BY P1.product_entry, P1.product_code) AS
> PE
> GROUP BY PE.product_code, PE.next_product_entry) AS PR
> INNER JOIN
> ProductEntries AS P
> ON P.product_code = PR.product_code AND
> P.product_entry >= PR.start_product_entry AND
> (PR.end_product_entry IS NULL OR
> P.product_entry < PR.end_product_entry)
> GROUP BY PR.product_code, PR.start_product_entry
> ORDER BY start_product_entry
> product_code start_product_entry end_product_entry avg_product_quality
> A 1 3 76.666666
> B 4 5 75.000000
> C 6 6 80.000000
> D 7 7 80.000000
> A 8 8 50.000000
> C 9 9 70.000000
> --
> JAG|||That is too much work! Let's move the average calculation into a
scalar subquery that will be done last, after all the clusters are
found. The little-used = ALL predicate can replace a lot of your
logic. And we pull up the usual Sequence auxiliary table.

SELECT prod_code, MIN(start) AS start, finish,
(SELECT AVG(CAST(prod_quality AS DECIMAL(8,4)))
FROM ProductEntries AS P3
WHERE P3.prod_entry
BETWEEN MIN(start)
AND X.finish) AS avg_quality
FROM (SELECT P1.prod_code, S1.seq, MAX(S2.seq) AS finish
FROM ProductEntries AS P1, Sequence AS S1, Sequence AS S2
WHERE S1.seq <= S2.seq
AND S2.seq <= (SELECT MAX(prod_entry) FROM ProductEntries)
AND P1.prod_code
= ALL (SELECT P2.prod_code
FROM ProductEntries AS P2
WHERE P2.prod_entry BETWEEN S1.seq AND S2.seq)
GROUP BY P1.prod_code, S1.seq)
AS X (prod_code, start, finish)
GROUP BY prod_code, finish;

Another version requires two sentinal values
--
INSERT INTO ProductEntries VALUES (0, '?', 0);
INSERT INTO ProductEntries VALUES (10, '?', 0);

SELECT DISTINCT P1.prod_code, S1.seq AS start, S2.seq AS finish,
(SELECT AVG(CAST(prod_quality AS DECIMAL(8,4)))
FROM ProductEntries AS P3
WHERE P3.prod_entry
BETWEEN S1.seq AND S2.seq) AS avg_quality
FROM ProductEntries AS P1,
(SELECT seq FROM Sequence
UNION ALL SELECT 0) AS S1, Sequence AS S2
WHERE S1.seq <= S2.seq
AND S2.seq <= (SELECT MAX(prod_entry) + 1 FROM ProductEntries)
AND P1.prod_code
<> (SELECT P3.prod_code
FROM ProductEntries AS P3
WHERE P3.prod_entry = S1.seq - 1)
AND P1.prod_code
<> (SELECT P4.prod_code
FROM ProductEntries AS P4
WHERE P4.prod_entry = S2.seq + 1)
AND P1.prod_code
= ALL (SELECT P2.prod_code
FROM ProductEntries AS P2
WHERE P2.prod_entry BETWEEN S1.seq AND S2.seq);|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1102618417.146169.127100@.c13g2000cwb.googlegr oups.com...
> That is too much work! Let's move the average calculation into a
> scalar subquery that will be done last, after all the clusters are
> found. The little-used = ALL predicate can replace a lot of your
> logic. And we pull up the usual Sequence auxiliary table.
> SELECT prod_code, MIN(start) AS start, finish,
> (SELECT AVG(CAST(prod_quality AS DECIMAL(8,4)))
> FROM ProductEntries AS P3
> WHERE P3.prod_entry
> BETWEEN MIN(start)
> AND X.finish) AS avg_quality
> FROM (SELECT P1.prod_code, S1.seq, MAX(S2.seq) AS finish
> FROM ProductEntries AS P1, Sequence AS S1, Sequence AS S2
> WHERE S1.seq <= S2.seq
> AND S2.seq <= (SELECT MAX(prod_entry) FROM ProductEntries)
> AND P1.prod_code
> = ALL (SELECT P2.prod_code
> FROM ProductEntries AS P2
> WHERE P2.prod_entry BETWEEN S1.seq AND S2.seq)
> GROUP BY P1.prod_code, S1.seq)
> AS X (prod_code, start, finish)
> GROUP BY prod_code, finish;
> Another version requires two sentinal values
> --
> INSERT INTO ProductEntries VALUES (0, '?', 0);
> INSERT INTO ProductEntries VALUES (10, '?', 0);
> SELECT DISTINCT P1.prod_code, S1.seq AS start, S2.seq AS finish,
> (SELECT AVG(CAST(prod_quality AS DECIMAL(8,4)))
> FROM ProductEntries AS P3
> WHERE P3.prod_entry
> BETWEEN S1.seq AND S2.seq) AS avg_quality
> FROM ProductEntries AS P1,
> (SELECT seq FROM Sequence
> UNION ALL SELECT 0) AS S1, Sequence AS S2
> WHERE S1.seq <= S2.seq
> AND S2.seq <= (SELECT MAX(prod_entry) + 1 FROM ProductEntries)
> AND P1.prod_code
> <> (SELECT P3.prod_code
> FROM ProductEntries AS P3
> WHERE P3.prod_entry = S1.seq - 1)
> AND P1.prod_code
> <> (SELECT P4.prod_code
> FROM ProductEntries AS P4
> WHERE P4.prod_entry = S2.seq + 1)
> AND P1.prod_code
> = ALL (SELECT P2.prod_code
> FROM ProductEntries AS P2
> WHERE P2.prod_entry BETWEEN S1.seq AND S2.seq);

Less work? Debatable. Also, this won't work if the product_entry values
aren't consecutive.

--
JAG|||>> Less work? Debatable. <<

Fewer nesting levels should be a bit faster. But trying to find the
start and finish points is going to get really bad as the number of row
increases.

>> Also, this won't work if the product_entry values
aren't consecutive. <<

It depends on the sequence of tests having no gaps.

This is one that might be better done with a cursor and a WHILE loop
that accumulates a count and total of each quality test to a working
table.

--CELKO--
Please post DDL in a human-readable format and not a machne-generated
one. This way people do not have to guess what the keys, constraints,
Declarative Referential Integrity, datatypes, etc. in your schema are.
Sample data is also a good idea, along with clear specifications.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
news:1102630139.1f7348d9b8d0e1527f37d16587e3cecc@.t eranews...
> >> Less work? Debatable. <<
> Fewer nesting levels should be a bit faster. But trying to find the
> start and finish points is going to get really bad as the number of row
> increases.
> >> Also, this won't work if the product_entry values
> aren't consecutive. <<
> It depends on the sequence of tests having no gaps.
> This is one that might be better done with a cursor and a WHILE loop
> that accumulates a count and total of each quality test to a working
> table.

You could be right but bite your tongue!

--
JAG

> --CELKO--
> Please post DDL in a human-readable format and not a machne-generated
> one. This way people do not have to guess what the keys, constraints,
> Declarative Referential Integrity, datatypes, etc. in your schema are.
> Sample data is also a good idea, along with clear specifications.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Actually if this was an ongoing query: performance-wise it would be best to
have a trigger or "phase shift" grouping id set as part of the insert
operation.

"John Gilson" <jag@.acm.org> wrote in message
news:bo4ud.74477$Vk6.62153@.twister.nyc.rr.com...
> "--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
> news:1102630139.1f7348d9b8d0e1527f37d16587e3cecc@.t eranews...
>> >> Less work? Debatable. <<
>>
>> Fewer nesting levels should be a bit faster. But trying to find the
>> start and finish points is going to get really bad as the number of row
>> increases.
>>
>> >> Also, this won't work if the product_entry values
>> aren't consecutive. <<
>>
>> It depends on the sequence of tests having no gaps.
>>
>> This is one that might be better done with a cursor and a WHILE loop
>> that accumulates a count and total of each quality test to a working
>> table.
> You could be right but bite your tongue!
> --
> JAG
>> --CELKO--
>> Please post DDL in a human-readable format and not a machne-generated
>> one. This way people do not have to guess what the keys, constraints,
>> Declarative Referential Integrity, datatypes, etc. in your schema are.
>> Sample data is also a good idea, along with clear specifications.
>>
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>> Don't just participate in USENET...get rewarded for it!|||>> Actually if this was an ongoing query: performance-wise it would be
best to have a trigger or "phase shift" grouping id set as part of the
insert operation. <<

My impulse is for a "cluster group number" column as each test is done.
Look to see if the current quality test is on the same product as the
most recent one, etc.

--CELKO--
Please post DDL in a human-readable format and not a machne-generated
one. This way people do not have to guess what the keys, constraints,
Declarative Referential Integrity, datatypes, etc. in your schema are.
Sample data is also a good idea, along with clear specifications.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Query to get the names of items with different levels

hi

I've a table with coln names

ID
Name
ParentID
Level

I've list with different levels

say

ex.

the Data is:-

ID Name ParentID Level
1 Root null 1
2 Trunk 1 2
3 Branch 2 3
4 Leaf 3 4
5 Stem 3 4

Now I want to show this data as

Root -> Trunk -> Branch -> Leaf

How to write the query for getting the Names for different levels for corresponding ParentID...select case when level is 1 then Name end as Root,
case when level is 2 then Name end as Trunk,
case when level is 3 then Name end as Branch,
case when level is 4 then Name end as Leaf,
case when level is 5 then Name end as Stem

Quote:

Originally Posted by sree078

hi

I've a table with coln names

ID
Name
ParentID
Level

I've list with different levels

say

ex.

the Data is:-

ID Name ParentID Level
1 Root null 1
2 Trunk 1 2
3 Branch 2 3
4 Leaf 3 4
5 Stem 3 4

Now I want to show this data as

Root -> Trunk -> Branch -> Leaf

How to write the query for getting the Names for different levels for corresponding ParentID...

Query table for cheapest items (but a little more complicated than that)

I'm not sure if this is even possible but can i pull out rows from two tables (that have a one-to-many relationship) but only if they satisfy a few conditions.

tblWine tblSources
ID Name ID WineID Source Price Status
------ -------------
1 Le Dome 1 1 Smith 100.00 IB
2 Teyssier 2 1 Jones 110.00 IB
3 Muscat 3 1 Hill 100.00 DP
4 2 Smith 135.00 DP
5 2 Hill 125.00 DP

I only want to pull out row that contain the cheapest wine for their status. So the result would look something like this.

tblWineSources
WineID Name SourceID Source Price Status
-------------------
1 Le Dome 1 Smith 110.00 IB
1 Le Dome 3 Hill 100.00 DP
2 Teyssier 5 Hill 125.00 DPPlease check your Resultsets they do not represent the requirement you stated...
BTW, for your help ...

CREATE TABLE tblWine(
ID VARCHAR(10)
,NAME VARCHAR(20))
Go
CREATE TABLE tblSources(
ID VARCHAR(10)
,WINEID VARCHAR(10)
,SOURCE VARCHAR(20)
,PRICE INT
,STATUS VARCHAR(10))
Go
INSERT INTO tblWine VALUES('1','Le Dome')
INSERT INTO tblWine VALUES('2','Teyssier')
INSERT INTO tblWine VALUES('3','Muscat')

INSERT INTO tblSources VALUES('1','1','Smith',100.00,'IB')
INSERT INTO tblSources VALUES('2','1','Jones',110.00,'IB')
INSERT INTO tblSources VALUES('3','1','Hill',100.00,'DP')
INSERT INTO tblSources VALUES('4','2','Smith',135.00,'DP')
INSERT INTO tblSources VALUES('5','2','Hill',125.00,'DP')
GO
--The required Query Place--
SELECT tblSources.WINEID
,tblWine.Name
,tblSources.ID as SourceID
,tblSources.Source
,tblSources.Status
,tblSources.Price as Maxprice
FROM tblWine
INNER JOIN tblSources
ON tblWine.ID=tblSources.WINEID

GROUP BY tblSources.WINEID
,tblWine.Name
,tblSources.ID
,tblSources.Source
,tblSources.Status
,tblSources.Price
----------

Go
DROP TABLE tblWine
DROP TABLE tblSources|||:) I realise that. That's because I don't want to return all the rows in the join. I only need the rows that have the cheapest price for their status. Since my first post I've managed to solve part of the problem.

SELECT
tblWine.ID AS WineID,
tblWine.Name,
tblSources.ID AS SourceID,
tblSources.Source,
tblSources.Status,
tblSources.Price
FROM
tblSources
LEFT OUTER JOIN tblWine ON tblSources.WineID = tblWine.ID
GROUP BY
tblWine.ID,
tblWine.Name,
tblSources.ID,
tblSources.Source,
tblSources.Status,
tblSources.Price
HAVING
tblSources.ID IN
(
SELECT
TOP 1
subSources.ID
FROM
tblSources subSources
WHERE
subSources.WineID = tblWine.ID
AND subSources.Status = 'IB'
ORDER BY
subSources.Price ASC
)
OR tblSources.ID IN
(
SELECT
TOP 1
subSources.ID
FROM
tblSources subSources
WHERE
subSources.WineID = tblWine.ID
AND subSources.Status = 'DP'
ORDER BY
subSources.Price ASC
)
ORDER BY
tblWine.ID ASC,
tblSources.ID ASC

Thanks for you help though.

The problem now is that I've made these tables simpler for the purpose of this post so not to confuse the issue. There is actually another column in tblSources which I need to check is unique just like status. This additional column can have 1 of 8 different values, and the only way I currently know how to do it is so expand the query above to accommodate 16 subqueries in the HAVING clause, which doesn't seem ideal, or is it?

So ...

tblWine tblSources
ID Name ID WineID Source Price Status Format
------ -------------
1 Le Dome 1 1 Smith 100.00 IB Bottle
2 Teyssier 2 1 Jones 110.00 IB Bottle
3 Muscat 3 1 Hill 100.00 DP Bottle
4 2 Smith 135.00 DP Bottle
5 2 Hill 125.00 DP Magnum

... would return ...

tblWineSources
WineID Name SourceID Source Price Status Format
-------------------
1 Le Dome 1 Smith 110.00 IB Bottle
1 Le Dome 3 Hill 100.00 DP Bottle
2 Teyssier 5 Smith 135.00 DP Bottle
2 Teyssier 5 Hill 125.00 DP Magnum

So because there are two sources for Le Dome / IB / Bottle, only the cheapest record would be shown.|||If no one can help with the above, could someone tell me if the following is even possible?

How do I find the IDs of the cheapest Format/Status pair?

So ...

tblSources
ID Price Format Status
-----------
1540 100.00 Bottles IB
1541 110.00 Bottles DP
1542 105.00 Bottles IB
1543 105.00 Bottles DP
1544 115.00 Magnums IB
1545 110.00 Magnums IB

... would result in ...

Results
ID
--
1540
1543
1545

I could use a query for each combonation but the actual data can contain 1 of 2 different statuses or 1 of 8 different formats.|||try this as the solution to your problem in post #1select W.ID as WineID
, W.Name
, S.ID as SourceID
, S.Source
, S.Price
, S.Status
from tblWine as W
inner
join tblSources as S
on S.WineID = W.ID
and S.Price =
( select min(Price)
from tblSources
where WineID = S.WineID
and Status = S.Status )|||But wouldn't that mean that if their were two rows that had the same price and status, then they would both be included? One requirement which I've forgot to include is that if two rows have the same price and status but are from two different sources, then only one row would be shown.|||But wouldn't that mean that if their were two rows that had the same price and status, then they would both be included? yep, that is correct

One requirement which I've forgot to include is that if two rows have the same price and status but are from two different sources, then only one row would be shown.which source?|||any source, doesn't really matter. but as long as only one is shown.|||thanks to you, i've sussed it!

here's an example of what i did (i used a specific wine with many sources, in this case it had an ID of 11)

SELECT
tblSources.*
FROM
tblSources
WHERE
tblSources.WineID = 11
AND tblSources.ID =
(
SELECT
TOP 1
subSources.ID
FROM
tblSources AS subSources
WHERE
subSources.WineID = tblSources.WineID
AND subSources.Format = tblSources.Format
AND subSources.Status = tblSources.Status
AND subSources.CasePrice =
(
SELECT
MIN(sub2Sources.CasePrice)
FROM
tblSourcesAS sub2Sources
WHERE
sub2Sources.WineID = subSources.WineID
AND sub2Sources.Format = subSources.Format
AND sub2Sources.Status = subSources.Status
)
)|||select W.ID as WineID
, W.Name
, S.ID as SourceID
, S.Source
, S.Price
, S.Status
from tblWine as W
inner
join tblSources as S
on S.WineID = W.ID
and S.Price =
( select min(Price)
from tblSources
where WineID = S.WineID
and Status = S.Status )
and S.Source =
( select min(Source)
from tblSources
where WineID = S.WineID
and Status = S.Status
and Price =
( select min(Price)
from tblSources
where WineID = S.WineID
and Status = S.Status ) )