Help building query

Hello,
I need help in finding the best way to build a query for the following.
I have a table let's call EQPDTL.
This table contains all equipment usage for a company.
The table contains the followin fields:
CompanyNo
EquipNo
JobNo
DateEntered
RateCode (Can be H, I, B, Z, O, W)
DayOfWeek (1-7)
HoursUsed
TypeOfCost (0, 1, 2, 3, 4, or 5)
Here is a select statement that narrows down the actual records that I
need to work with. In english the records need to belong to CompanyNo X,
JobNo X, and it has to be a RateCode of (H, I, B, Z, O, or W), the type of
cost must not equal 0, and we only want records between the dates of X and
Y.
SELECT *
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR
RateCode = 'Z' OR RateCode = 'O' OR RateCode = 'W'))
AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
So now that we have the list of records now I need to get some
summarization data and have it in the following format.
1) For each piece of equipment I need to know the hours it was available.
This is simply the number of days it was used times 8. This would be the
first column.
2) For each piece of equipment I need to know the number of HoursUsed
based on the RateCode. So if equipment X had hours for 3 different rate
codes then at a minimum there would be at least 3 records in the original
dataset to work with... there could be more. There would be 6 columns, 1
for each RateCode (H, I, B, Z, O, W)
3) There would be another colum that has the total hours for all of the
RateCodes in #2 above.
4) There would be a final column that gives the percent utilized which is
calculated from the HoursUsed of RateCode H + O divided by AvailableHours
times 100.
The following query kinda gives me a summary look at the RateCodes where
each RateCode type summary is a record.
SELECT CompanyNo, EquipNo, RateCode, SUM(HoursUsed) AS Hours
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR RateCode = 'Z' OR
RateCode = 'O' OR RateCode = 'W')) AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
GROUP BY CompanyNo, EquipNo, RateCode
ORDER BY CompanyNo, EquipNo, RateCode
In the end I would like something like this as the results grouped by the
CompanyNo, JobNo, and EquipNo:
EquipNo, AvailableHours, H Hours, I Hours, O Hours, W Hours, Z Hours, B
Hours, Total Hours, Percent Utilized.
However I am unsure on what I should to to get this into more of a column
based results for each piece of equipment as well as get the other
results.
I can definitely take the first select statement and write an external
program to build a resulting table. I am sure I can also do a series of
temp tables to get a final result.
What I would like assistance on is how you would recommend doing this...
what do you believe would be the best method. I run into this type of
need several times a year and I really would like find a solution I can
use in situations as this.
Thank you for your time and willingness to help.
Greg
On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:

>Hello,
>I need help in finding the best way to build a query for the following.
(very big snip)
>What I would like assistance on is how you would recommend doing this...
>what do you believe would be the best method. I run into this type of
>need several times a year and I really would like find a solution I can
>use in situations as this.
>Thank you for your time and willingness to help.
Hi Greg,
This kind of problem is typically known as crosstab or pivot. The usual
recommendation is to do this in the client, as it's basically just
another formatting issue. However, there are times when that's not
practical; in that case, you can use a variation of this simple example:
USE pubs
go
SELECT pub_id,
SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
business_sales,
SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
psychology_sales,
SUM(CASE WHEN type NOT IN ('business','psychology') THEN
ytd_sales END) AS other_sales,
SUM(ytd_sales) AS total_sales
FROM titles
GROUP BY pub_id
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||In article <1q14i1porio6ug54pglonpop1k6emrec6s@.4ax.com>,
hugo@.pe_NO_rFact.in_SPAM_fo says...
> On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
> (very big snip)
> Hi Greg,
> This kind of problem is typically known as crosstab or pivot. The usual
> recommendation is to do this in the client, as it's basically just
> another formatting issue. However, there are times when that's not
> practical; in that case, you can use a variation of this simple example:
> USE pubs
> go
> SELECT pub_id,
> SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
> business_sales,
> SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
> psychology_sales,
> SUM(CASE WHEN type NOT IN ('business','psychology') THEN
> ytd_sales END) AS other_sales,
> SUM(ytd_sales) AS total_sales
> FROM titles
> GROUP BY pub_id
> go
>
> Best, Hugo
>
Hugo,
Thanks for the reply... I ended up going down the client route. I
appreciate your example... I started down that route as well and then just
decided I like the client solution for my needs.
Greg

Help building query

Hello,
I need help in finding the best way to build a query for the following.
I have a table let's call EQPDTL.
This table contains all equipment usage for a company.
The table contains the followin fields:
CompanyNo
EquipNo
JobNo
DateEntered
RateCode (Can be H, I, B, Z, O, W)
DayOfWeek (1-7)
HoursUsed
TypeOfCost (0, 1, 2, 3, 4, or 5)
Here is a select statement that narrows down the actual records that I
need to work with. In english the records need to belong to CompanyNo X,
JobNo X, and it has to be a RateCode of (H, I, B, Z, O, or W), the type of
cost must not equal 0, and we only want records between the dates of X and
Y.
SELECT *
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR
RateCode = 'Z' OR RateCode = 'O' OR RateCode = 'W'))
AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
So now that we have the list of records now I need to get some
summarization data and have it in the following format.
1) For each piece of equipment I need to know the hours it was available.
This is simply the number of days it was used times 8. This would be the
first column.
2) For each piece of equipment I need to know the number of HoursUsed
based on the RateCode. So if equipment X had hours for 3 different rate
codes then at a minimum there would be at least 3 records in the original
dataset to work with... there could be more. There would be 6 columns, 1
for each RateCode (H, I, B, Z, O, W)
3) There would be another colum that has the total hours for all of the
RateCodes in #2 above.
4) There would be a final column that gives the percent utilized which is
calculated from the HoursUsed of RateCode H + O divided by AvailableHours
times 100.
The following query kinda gives me a summary look at the RateCodes where
each RateCode type summary is a record.
SELECT CompanyNo, EquipNo, RateCode, SUM(HoursUsed) AS Hours
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR RateCode = 'Z' OR
RateCode = 'O' OR RateCode = 'W')) AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
GROUP BY CompanyNo, EquipNo, RateCode
ORDER BY CompanyNo, EquipNo, RateCode
In the end I would like something like this as the results grouped by the
CompanyNo, JobNo, and EquipNo:
EquipNo, AvailableHours, H Hours, I Hours, O Hours, W Hours, Z Hours, B
Hours, Total Hours, Percent Utilized.
However I am unsure on what I should to to get this into more of a column
based results for each piece of equipment as well as get the other
results.
I can definitely take the first select statement and write an external
program to build a resulting table. I am sure I can also do a series of
temp tables to get a final result.
What I would like assistance on is how you would recommend doing this...
what do you believe would be the best method. I run into this type of
need several times a year and I really would like find a solution I can
use in situations as this.
Thank you for your time and willingness to help.
GregOn Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:

>Hello,
>I need help in finding the best way to build a query for the following.
(very big snip)
>What I would like assistance on is how you would recommend doing this...
>what do you believe would be the best method. I run into this type of
>need several times a year and I really would like find a solution I can
>use in situations as this.
>Thank you for your time and willingness to help.
Hi Greg,
This kind of problem is typically known as crosstab or pivot. The usual
recommendation is to do this in the client, as it's basically just
another formatting issue. However, there are times when that's not
practical; in that case, you can use a variation of this simple example:
USE pubs
go
SELECT pub_id,
SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
business_sales,
SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
psychology_sales,
SUM(CASE WHEN type NOT IN ('business','psychology') THEN
ytd_sales END) AS other_sales,
SUM(ytd_sales) AS total_sales
FROM titles
GROUP BY pub_id
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In article <1q14i1porio6ug54pglonpop1k6emrec6s@.4ax.com>,
hugo@.pe_NO_rFact.in_SPAM_fo says...
> On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
>
> (very big snip)
> Hi Greg,
> This kind of problem is typically known as crosstab or pivot. The usual
> recommendation is to do this in the client, as it's basically just
> another formatting issue. However, there are times when that's not
> practical; in that case, you can use a variation of this simple example:
> USE pubs
> go
> SELECT pub_id,
> SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
> business_sales,
> SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
> psychology_sales,
> SUM(CASE WHEN type NOT IN ('business','psychology') THEN
> ytd_sales END) AS other_sales,
> SUM(ytd_sales) AS total_sales
> FROM titles
> GROUP BY pub_id
> go
>
> Best, Hugo
>
Hugo,
Thanks for the reply... I ended up going down the client route. I
appreciate your example... I started down that route as well and then just
decided I like the client solution for my needs.
Gregsql

Help - View Trigger or Table Trigger

Hello All,
My application takes data readings every few seconds and stores a
couple of data values for piece of equipment in a table for historic
trending. The table is simlar to:
CREATE TABLE [DataTest] (
[DataID] [bigint] IDENTITY (1, 1) NOT NULL ,
[EquipmentID] [int] NOT NULL,
[DataTimestamp] [datetime] NOT NULL ,
[DataReading1] [decimal](18, 4) NOT NULL,
[DataReading2] [decimal](18, 4) NOT NULL
[DataReading3] [decimal](18, 4) NOT NULL
)
My application needs to alert users when data values fall outside of
acceptable thresholds. It also must allow users to view a history of
when these "alerts" have triggered.
I would like to utilize the database (SQL Server 2000) as much as
possible for this functionality. I originally thought I could create a
new View for each alert that users set. For example,
CREATE VIEW dbo.Alert_25
AS
SELECT * FROM DataTest WHERE DataReading1 > 200 AND DataReading2 < 500
WHERE EquipmentID = 1
Each view would then contain the history of when that alert triggered.
I then thought I could add a trigger to that View to notify the user
that the data has gone out of range. This is where things fall apart.
The inserts are happening on the main table not the view, so a trigger
on the view does not fire.
SO...here are my questions.
1. I would like to keep the trigger associated with the View if
possible. That way, if the user deletes the View the triggers can
easily be removed as well. Is there anyway for the trigger to fire if
it is on the View?
2. I guess my alternative would be to have both Views for the alert
histories and multiple triggers on the data table. Is this an
acceptable approach? Say the user where to create 100 different alerts.
What impact on the server would this have if this created 100 different
triggers on the same data table?
I appreciate your opinions and advice. If I am way off base all
together, I appreciate any other suggestions.
Thanks,
YofnikOn 15 Aug 2005 13:09:39 -0700, Yofnik wrote:
(snip)
>SO...here are my questions.
>1. I would like to keep the trigger associated with the View if
>possible. That way, if the user deletes the View the triggers can
>easily be removed as well. Is there anyway for the trigger to fire if
>it is on the View?
Hi Yofnik,
No. A view can have only an INSTEAD OF trigger defined on it, and that
will only fire when an insert, update or delete is executed with the
view as target.

>2. I guess my alternative would be to have both Views for the alert
>histories and multiple triggers on the data table. Is this an
>acceptable approach? Say the user where to create 100 different alerts.
>What impact on the server would this have if this created 100 different
>triggers on the same data table?
It would cause a dramatic slowdown of your data modifications. Keeping
it all in one trigger would also cause a slowdown, but not as much.
Anyway, I think you should also reconsider the first stetp. You mention
"alerting users" without going into the specifics, but I have a hunch
that you plan to send out an email. And sending email from a trigger is,
as Orwell would say, doubleplusungood practice. Not only because it will
slow down your inserts tremendously, but also becuase you are mingling
in-transaction and out-transaction actions. What happens if the
transaction fails and is rolled back immediately after the mail is sent?
Your best bet is probably to investigate what Notification Services can
do for you.
If NS can't be used in your case, then create your views, write a script
that checks for entries in each of the views without matching entry in
the "alert history" table and sends a mail if it finds one. Use Agent to
schedule this as a job that runs as often as you need it (depends on how
much time may pass between the measurement and the alert).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Sorry, I suppose I did not mention HOW the trigger would alert users. I
definitely was not planning on sending an email from the trigger. I
would like the trigger to simply put a new row in the AlertHistory
table with the AlertID and a flag indicating if it has been processed.
Then an external application can regularly poll this one table and grab
all alert IDs that have not been processed and send out an email. The
external application will then set the flag to indicate the alerts have
been processed.
With that said, is your last suggestion my best approach? Is there an
easy way to check for new rows in ALL views? I guess that would require
dynamic SQL then, huh?|||On 15 Aug 2005 13:42:25 -0700, Yofnik wrote:

>Sorry, I suppose I did not mention HOW the trigger would alert users. I
>definitely was not planning on sending an email from the trigger. I
>would like the trigger to simply put a new row in the AlertHistory
>table with the AlertID and a flag indicating if it has been processed.
>Then an external application can regularly poll this one table and grab
>all alert IDs that have not been processed and send out an email. The
>external application will then set the flag to indicate the alerts have
>been processed.
>With that said, is your last suggestion my best approach? Is there an
>easy way to check for new rows in ALL views? I guess that would require
>dynamic SQL then, huh?
Hi Yofnik,
If you only want to insert a row in the AlertHistory table, I'd probably
go for a trigger. But just ONE trigger - nod hundreds of 'em!
Either hard-code the limits that will cause an alert in the trigger (and
make sure that allchange requests go through one channel - i.e. you). If
you must have flexibility for the ussers to add, remove or change the
upper and lower limits for alerts, than create a second table to hold
all the limits, and join to that in the trigger to determine if any
alerts are generated. If you need more help on how to design this table
and how to code the trigger, give us some more information on your
current tables and data. Check out www.aspfaq.com/5006 to find out what
inforamtion we need to best address your question.
WRT your last question - steer clear of dynamic SQL! If you want to know
why, read http://www.sommarskog.se/dynamic_sql.html.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

help

Hi
If i use select * from table...i get the following
10 Energy Energy Energy Equipment & Services Oil & Gas
Drilling 10101010
10 Energy Energy Energy Equipment & Services Oil & Gas
Equipment & Svcs 10101020
10 Energy Energy Oil & Gas Integrated Oil &
Gas 10102010
10 Energy Energy Oil & Gas Oil & Gas
Exploration & Prod 10102020
10 Energy Energy Oil & Gas Oil & Gas Refining
& Mktg 10102030
10 Energy Energy Oil & Gas Oil & Gas Storage
& Transpor 10102040
10 Energy Energy Oil & Gas Coal & Consumable
Fuels 10102050
15 Materials Materials Chemicals Commodity
Chemicals 15101010
15 Materials Materials Chemicals Diversified
Chemicals 15101020
15 Materials Materials Chemicals Fertilizers & Agri
Chemicals 15101030
15 Materials Materials Chemicals Industrial
Gases 15101040
15 Materials Materials Chemicals Specialty
Chemicals 15101050
15 Materials Materials Construction Materials Construction
Materials 15102010
15 Materials Materials Containers & Packaging Metal & Glass
Containers 15103010
15 Materials Materials Containers & Packaging Paper
Packaging 15103020
15 Materials Materials Metals & Mining
Aluminum 15104010
15 Materials Materials Metals & Mining Diversified Metals
& Mining 15104020
15 Materials Materials Metals & Mining Gold
15104030
15 Materials Materials Metals & Mining Precious Metals &
Minerals 15104040
15 Materials Materials Metals & Mining Steel
15104050
15 Materials Materials Paper & Forest Products Forest
Products 15105010
15 Materials Materials Paper & Forest Products Paper
Products 15105020
but my output should be as follows
10 Energy Energy Energy Equipment & Services Oil & Gas
Drilling 10101010
Oil & Gas
Equipment & Svcs 10101020
10 Energy Energy Oil & Gas Integrated Oil &
Gas 10102010
Oil & Gas
Exploration & Prod 10102020
Oil & Gas Refining
& Mktg 10102030
Oil & Gas Storage
& Transpor 10102040
Coal & Consumable
Fuels 10102050
15 Materials Materials Chemicals Commodity
Chemicals 15101010
Diversified
Chemicals 15101020
Fertilizers & Agri
Chemicals 15101030
Industrial Gases
15101040
Specialty
Chemicals 15101050
15 Materials Materials Construction Materials Construction
Materials 15102010
Containers & Packaging Metal & Glass
Containers 15103010
Paper Packaging
15103020
15 Materials Materials Metals & Mining
Aluminum 15104010
Diversified Metals
& Mining 15104020
Gold
15104030
Precious Metals &
Minerals 15104040
Steel
15104050
15 Materials Materials Paper & Forest Products Forest
Products 15105010
Paper Products
15105020
can anyone tell me how 2 query for abovehi,
The output format needed for you is not that clear. Still feels like you
need a groupby, like
10 Energy Energy Energy
10 Energy Energy Oil & Gas
15 Materials Materials Chemicals
15 Materials Materials Construction Materials
...........................
...........................
In this case you need to try a group by,
SELECT 1,2,3,SUM(ANY VALUE FEILD) FROM TABLE
GROUP BY 1,2,3
Not sure if this is what you what, if not give more details like tbale
structure and optut you needed in good format...
Thanks,
Sree
"kalikoi@.gmail.com" wrote:

> Hi
>
> If i use select * from table...i get the following
>
> 10 Energy Energy Energy Equipment & Services Oil & Gas
> Drilling 10101010
> 10 Energy Energy Energy Equipment & Services Oil & Gas
> Equipment & Svcs 10101020
> 10 Energy Energy Oil & Gas Integrated Oil &
> Gas 10102010
> 10 Energy Energy Oil & Gas Oil & Gas
> Exploration & Prod 10102020
> 10 Energy Energy Oil & Gas Oil & Gas Refining
> & Mktg 10102030
> 10 Energy Energy Oil & Gas Oil & Gas Storage
> & Transpor 10102040
> 10 Energy Energy Oil & Gas Coal & Consumable
> Fuels 10102050
> 15 Materials Materials Chemicals Commodity
> Chemicals 15101010
> 15 Materials Materials Chemicals Diversified
> Chemicals 15101020
> 15 Materials Materials Chemicals Fertilizers & Agri
> Chemicals 15101030
> 15 Materials Materials Chemicals Industrial
> Gases 15101040
> 15 Materials Materials Chemicals Specialty
> Chemicals 15101050
> 15 Materials Materials Construction Materials Construction
> Materials 15102010
> 15 Materials Materials Containers & Packaging Metal & Glass
> Containers 15103010
> 15 Materials Materials Containers & Packaging Paper
> Packaging 15103020
> 15 Materials Materials Metals & Mining
> Aluminum 15104010
> 15 Materials Materials Metals & Mining Diversified Metals
> & Mining 15104020
> 15 Materials Materials Metals & Mining Gold
> 15104030
> 15 Materials Materials Metals & Mining Precious Metals &
> Minerals 15104040
> 15 Materials Materials Metals & Mining Steel
> 15104050
> 15 Materials Materials Paper & Forest Products Forest
> Products 15105010
> 15 Materials Materials Paper & Forest Products Paper
> Products 15105020
>
> but my output should be as follows
>
> 10 Energy Energy Energy Equipment & Services Oil & Gas
> Drilling 10101010
> Oil & Gas
> Equipment & Svcs 10101020
> 10 Energy Energy Oil & Gas Integrated Oil &
> Gas 10102010
> Oil & Gas
> Exploration & Prod 10102020
> Oil & Gas Refining
> & Mktg 10102030
> Oil & Gas Storage
> & Transpor 10102040
> Coal & Consumable
> Fuels 10102050
> 15 Materials Materials Chemicals Commodity
> Chemicals 15101010
> Diversified
> Chemicals 15101020
> Fertilizers & Agri
> Chemicals 15101030
> Industrial Gases
> 15101040
> Specialty
> Chemicals 15101050
> 15 Materials Materials Construction Materials Construction
> Materials 15102010
> Containers & Packaging Metal & Glass
> Containers 15103010
> Paper Packaging
> 15103020
> 15 Materials Materials Metals & Mining
> Aluminum 15104010
> Diversified Metals
> & Mining 15104020
> Gold
> 15104030
> Precious Metals &
> Minerals 15104040
> Steel
> 15104050
> 15 Materials Materials Paper & Forest Products Forest
> Products 15105010
> Paper Products
> 15105020
>
> can anyone tell me how 2 query for above
>|||Best bet is to place your output on a web page and link to it from here.
Newsgroup posters/readers dont handle long lines/ line breaks very well, so
anything longer than 70 or 80 characters becomes an unreadable mess.
Also, post your table DDL, and try to explain what you want with words, in
addition to visuals.
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:E492AFA1-A434-4101-A4D3-F7FB71E03EBA@.microsoft.com...
> hi,
> The output format needed for you is not that clear. Still feels like you
> need a groupby, like
> 10 Energy Energy Energy
> 10 Energy Energy Oil & Gas
> 15 Materials Materials Chemicals
> 15 Materials Materials Construction Materials
> ...........................
> ...........................
> In this case you need to try a group by,
> SELECT 1,2,3,SUM(ANY VALUE FEILD) FROM TABLE
> GROUP BY 1,2,3
> Not sure if this is what you what, if not give more details like tbale
> structure and optut you needed in good format...
> Thanks,
> Sree
>
>
>
> "kalikoi@.gmail.com" wrote:
>|||I got four table structures as follows
CREATE TABLE [dbo].[Sector] (
[SectorID] [int] NOT NULL ,
[SectorName] [varchar] (50)
)
the data is as follows
--
10 Energy
CREATE TABLE [dbo].[GroupMaster] (
[SectorID] [int] NOT NULL ,
[GroupID] [int] NOT NULL ,
[GroupName] [varchar] (50)
)
the data is as follows
--
10 1010 Energy
CREATE TABLE [dbo].[IndustryMaster] (
[GroupID] [int] NOT NULL ,
[IndustryID] [numeric](6, 0) NOT NULL ,
[IndustryName] [varchar] (50)
)
the data is as follows
---
1010 101010 Energy Equipment & Services
1010 101020 Oil & Gas
CREATE TABLE [dbo].[SubIndustryMaster] (
[IndustryID] [numeric](6, 0) NOT NULL ,
[SubIndustryID] [numeric](8, 0) NOT NULL ,
[SubIndustryName] [varchar] (30)
)
the data is as follows
----
101010 10101010 Oil & Gas Drilling
101010 10101020 Oil & Gas Equipment & Svcs
101020 10102010 Integrated Oil & Gas
101020 10102020 Oil & Gas Exploration & Prod
101020 10102030 Oil & Gas Refining & Mktg
101020 10102040 Oil & Gas Storage & Transpor
101020 10102050 Coal & Consumable Fuels
Now i want a ADO DataShape command which results the following
Energy
Energy
Energy Equipment & Services
Oil & Gas Drilling
Oil & Gas Equipment & Svcs
Oil & Gas
Integrated Oil & Gas
Oil & Gas Exploration & Prod
Oil & Gas Refining & Mktg
Oil & Gas Storage & Transpor
Coal & Consumable Fuels

help

Hi

If i use select * from table...i get the following

10 Energy Energy Energy Equipment & Services Oil & Gas
Drilling10101010
10 Energy Energy Energy Equipment & Services Oil & Gas
Equipment & Svcs10101020
10 Energy Energy Oil & Gas Integrated Oil &
Gas10102010
10 Energy Energy Oil & Gas Oil & Gas
Exploration & Prod10102020
10 Energy Energy Oil & Gas Oil & Gas Refining
& Mktg10102030
10 Energy Energy Oil & Gas Oil & Gas Storage
& Transpor10102040
10 Energy Energy Oil & Gas Coal & Consumable
Fuels10102050
15 Materials Materials Chemicals Commodity
Chemicals15101010
15 Materials Materials Chemicals Diversified
Chemicals15101020
15 Materials Materials Chemicals Fertilizers & Agri
Chemicals15101030
15 Materials Materials Chemicals Industrial
Gases15101040
15 Materials Materials Chemicals Specialty
Chemicals15101050
15 Materials Materials Construction Materials Construction
Materials15102010
15 Materials Materials Containers & Packaging Metal & Glass
Containers15103010
15 Materials Materials Containers & Packaging Paper
Packaging15103020
15 Materials Materials Metals & Mining
Aluminum15104010
15 Materials Materials Metals & Mining Diversified Metals
& Mining15104020
15 Materials Materials Metals & Mining Gold15104030
15 Materials Materials Metals & Mining Precious Metals &
Minerals15104040
15 Materials Materials Metals & Mining Steel15104050
15 Materials Materials Paper & Forest Products Forest
Products15105010
15 Materials Materials Paper & Forest Products Paper
Products15105020

but my output should be as follows

10 Energy Energy Energy Equipment & Services Oil & Gas
Drilling10101010
Oil & Gas Equipment & Svcs10101020
10 Energy Energy Oil & Gas Integrated Oil &
Gas10102010
Oil & Gas Exploration & Prod10102020
Oil & Gas Refining & Mktg10102030
Oil & Gas Storage & Transpor10102040
Coal & Consumable Fuels10102050
15 Materials Materials Chemicals Commodity
Chemicals15101010
Diversified Chemicals15101020
Fertilizers & Agri Chemicals15101030
Industrial Gases15101040
Specialty Chemicals15101050
15 Materials Materials Construction Materials Construction
Materials15102010
Containers & Packaging Metal & Glass Containers15103010
Paper Packaging15103020
15 Materials Materials Metals & Mining
Aluminum15104010
Diversified Metals & Mining15104020
Gold15104030
Precious Metals & Minerals15104040
Steel15104050
15 Materials Materials Paper & Forest Products Forest
Products15105010
Paper Products15105020

can anyone tell me how 2 query for abovekalikoi@.gmail.com wrote:

> Hi
> If i use select * from table...i get the following
>
> 10 Energy Energy Energy Equipment & Services Oil & Gas
> Drilling10101010
> 10 Energy Energy Energy Equipment & Services Oil & Gas
> Equipment & Svcs10101020
> 10 Energy Energy Oil & Gas Integrated Oil &
> Gas10102010
> 10 Energy Energy Oil & Gas Oil & Gas
> Exploration & Prod10102020
> 10 Energy Energy Oil & Gas Oil & Gas Refining
> & Mktg10102030
> 10 Energy Energy Oil & Gas Oil & Gas Storage
> & Transpor10102040
> 10 Energy Energy Oil & Gas Coal & Consumable
> Fuels10102050
> 15 Materials Materials Chemicals Commodity
> Chemicals15101010
> 15 Materials Materials Chemicals Diversified
> Chemicals15101020
> 15 Materials Materials Chemicals Fertilizers & Agri
> Chemicals15101030
> 15 Materials Materials Chemicals Industrial
> Gases15101040
> 15 Materials Materials Chemicals Specialty
> Chemicals15101050
> 15 Materials Materials Construction Materials Construction
> Materials15102010
> 15 Materials Materials Containers & Packaging Metal & Glass
> Containers15103010
> 15 Materials Materials Containers & Packaging Paper
> Packaging15103020
> 15 Materials Materials Metals & Mining
> Aluminum15104010
> 15 Materials Materials Metals & Mining Diversified Metals
> & Mining15104020
> 15 Materials Materials Metals & Mining Gold15104030
> 15 Materials Materials Metals & Mining Precious Metals &
> Minerals15104040
> 15 Materials Materials Metals & Mining Steel15104050
> 15 Materials Materials Paper & Forest Products Forest
> Products15105010
> 15 Materials Materials Paper & Forest Products Paper
> Products15105020
>
> but my output should be as follows
>
> 10 Energy Energy Energy Equipment & Services Oil & Gas
> Drilling10101010
> Oil & Gas Equipment & Svcs10101020
> 10 Energy Energy Oil & Gas Integrated Oil &
> Gas10102010
> Oil & Gas Exploration & Prod10102020
> Oil & Gas Refining & Mktg10102030
> Oil & Gas Storage & Transpor10102040
> Coal & Consumable Fuels10102050
> 15 Materials Materials Chemicals Commodity
> Chemicals15101010
> Diversified Chemicals15101020
> Fertilizers & Agri Chemicals15101030
> Industrial Gases15101040
> Specialty Chemicals15101050
> 15 Materials Materials Construction Materials Construction
> Materials15102010
> Containers & Packaging Metal & Glass Containers15103010
> Paper Packaging15103020
> 15 Materials Materials Metals & Mining
> Aluminum15104010
> Diversified Metals & Mining15104020
> Gold15104030
> Precious Metals & Minerals15104040
> Steel15104050
> 15 Materials Materials Paper & Forest Products Forest
> Products15105010
> Paper Products15105020
>
> can anyone tell me how 2 query for above

That's just presentational stuff. It is called "banding" in reports and
even the most basic reporting tool will do it for you. There isn't much
cause to do it in SQL and although it is certainly possible it will be
needlessly complex to do and won't perform well.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Small nit --

Whenever posting to a newsgroup try using headers a that are more
descriptive than just 'help'. I assume about 95% of
the people posting here require help. This'll make it easier for people
who are browsing old articles.

thanks,

Crazy|||I got four table structures as follows

CREATE TABLE [dbo].[Sector] (
[SectorID] [int] NOT NULL ,
[SectorName] [varchar] (50)
)

the data is as follows
-------
10Energy

CREATE TABLE [dbo].[GroupMaster] (
[SectorID] [int] NOT NULL ,
[GroupID] [int] NOT NULL ,
[GroupName] [varchar] (50)
)

the data is as follows
--------
10 1010 Energy

CREATE TABLE [dbo].[IndustryMaster] (
[GroupID] [int] NOT NULL ,
[IndustryID] [numeric](6, 0) NOT NULL ,
[IndustryName] [varchar] (50)
)

the data is as follows
--------------
1010101010Energy Equipment & Services
1010101020Oil & Gas

CREATE TABLE [dbo].[SubIndustryMaster] (
[IndustryID] [numeric](6, 0) NOT NULL ,
[SubIndustryID] [numeric](8, 0) NOT NULL ,
[SubIndustryName] [varchar] (30)
)

the data is as follows
----------------
10101010101010Oil & Gas Drilling
10101010101020Oil & Gas Equipment & Svcs
10102010102010Integrated Oil & Gas
10102010102020Oil & Gas Exploration & Prod
10102010102030Oil & Gas Refining & Mktg
10102010102040Oil & Gas Storage & Transpor
10102010102050Coal & Consumable Fuels

Now i want a ADO DataShape command which results the following

Energy
Energy
Energy Equipment & Services
Oil & Gas Drilling
Oil & Gas Equipment & Svcs

Oil & Gas
Integrated Oil & Gas
Oil & Gas Exploration & Prod
Oil & Gas Refining & Mktg
Oil & Gas Storage & Transpor
Coal & Consumable Fuels|||(kalikoi@.gmail.com) writes:
> Now i want a ADO DataShape command which results the following
>
> Energy
> Energy
> Energy Equipment & Services
> Oil & Gas Drilling
> Oil & Gas Equipment & Svcs
> Oil & Gas
> Integrated Oil & Gas
> Oil & Gas Exploration & Prod
> Oil & Gas Refining & Mktg
> Oil & Gas Storage & Transpor
> Coal & Consumable Fuels

Hm, maybe you should try an ADO newsgroup. I've read about MS Data Shape,
but I should probably not suggest solutions for it...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx