|
Home > Archive > MS SQL Server MSEQ > October 2006 > sql query single table
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
sql query single table
|
|
| madgame 2006-10-24, 6:49 pm |
| I am trying to run a report. The requestor has specifics on the way it
should be formatted on output. I'm going to try and explain what he's
looking for as well as give the example of output which was given me. The
"Central Host" is the system whose agcprefix = DAC. Each "Central Host"
record should be a unique sysid. An offboard is considered a sysid which
does not have an agcprefix = DAC. An offboard always has a central host, but
a central host does not always have an offboard. Also he only wants the
release level from either type to be like 'DK%'.
He would like the central host row of information, followed by any offboards
for that central hosts with the central host field blank. If there are no
offboards then that field would be blank. He also wants totals as specified
below.
Sample output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Central Host OffBoard Release Level Platform Type
C126606 DK041B r3
C126568 DK041B Linux
C222222 DK053B alpha
C333333 DK053 r3
Total Platforms 4
Total offboards 1
Total onboards 2 - Onboards in this case are systems with no offboards.
Total DK041B 2
Total DK053B 1
Total DK053 1
Total Linux 1
Total alpha 1
Total r3 2
Create table
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE & #91;DsdaSysConfigPro
file] (
[serial] [varchar] (8) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[sysid] [varchar] (50) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[agcprefix] [varchar] (50) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[release] [varchar] (50) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
[platform] [varchar] (50) COLLATE SQL_Latin1_General_C
P1_CI_AS NULL ,
) ON [PRIMARY]
GO
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
table data
serial,sysid,agcpref
ix,release,platform --- In the DB sysid is what the
agcprefix, release, and platform information refer to, not serial.
C126568,C126568,DAD,
DK041B,linux
C126568,C126568,DAX,
DX041B,linux
C126568,C126606,DAC,
DC041B,r3
C126568,C126606,DAD,
DK041B,r3
C126568,C126606,DAP,
DP041B,r3
C126568,C126606,DAZ,
DZ041B,r3
C126606,C126606,DAD,
DK041B,r3
C126606,C126606,DAP,
DP041B,r3
C126606,C126606,DAC,
DC041B,r3
C126606,C126606,DAZ,
DZ041B,r3
C126606,C126568,DAD,
DK041B,linux
Thanks for any help.
| |
| Hugo Kornelis 2006-10-24, 6:49 pm |
| On Thu, 28 Sep 2006 13:52:01 -0700, madgame wrote:
>I am trying to run a report. The requestor has specifics on the way it
>should be formatted on output. I'm going to try and explain what he's
>looking for as well as give the example of output which was given me. The
>"Central Host" is the system whose agcprefix = DAC. Each "Central Host"
>record should be a unique sysid. An offboard is considered a sysid which
>does not have an agcprefix = DAC. An offboard always has a central host, but
>a central host does not always have an offboard. Also he only wants the
>release level from either type to be like 'DK%'.
>
>He would like the central host row of information, followed by any offboards
>for that central hosts with the central host field blank. If there are no
>offboards then that field would be blank. He also wants totals as specified
>below.
>
>Sample output
(snip)
Hi Madgame,
First of all - thanks for posting the CREATE TABLE statement. Does your
real table also not have a key and allow NULL in all columns? If that's
the case, you should make improving the design your top priority.
Unfortunately, even with the description and the sample output, I'm not
sure if I really understand what you're trying to achieve. For isntance,
where do the rows with central host C222222 and C333333 in the sample
output come from? Is the sample inpus posted incomplete?
I *think* that the query below will return the desired results. I didn't
test is myself, since you didn't post the sample data in the form of
INSERT statements and I'm too lazy to type them myself. <g>
SELECT CASE WHEN agcprefix = 'DAC'
THEN sysid
ELSE ''
END AS "Central Host",
CASE WHEN agcprefix = 'DAC'
THEN ''
ELSE 'sysid'
END AS "OffBoard",
release AS "Release Level",
platform AS "Platformm Type"
FROM (SELECT DISTINCT sysid, agcprefix, release, platform
FROM DsdaSysConfigProfile
WHERE release LIKE 'DK%') AS D
--
Hugo Kornelis, SQL Server MVP
| |
| madgame 2006-10-24, 6:49 pm |
| Thanks for the help Hugo. The real table does have a key and allow nulls as
far as I know. Though I'm not the db admin, I'm a grunt that uses it to pull
reports. :-D. I'm still fairly new to SQL. I checked one of the pages you
recommended for getting requests like this answered which is how I got the
create table on here for ya. I seen mention of INSERT, but there's no
example so I'm not sure how to go about that. Can you give me an example of
how to use INSERT to post data which would be more helpful for you, to help
me? Thanks again.
"Hugo Kornelis" wrote:
> On Thu, 28 Sep 2006 13:52:01 -0700, madgame wrote:
>
> (snip)
>
> Hi Madgame,
>
> First of all - thanks for posting the CREATE TABLE statement. Does your
> real table also not have a key and allow NULL in all columns? If that's
> the case, you should make improving the design your top priority.
>
> Unfortunately, even with the description and the sample output, I'm not
> sure if I really understand what you're trying to achieve. For isntance,
> where do the rows with central host C222222 and C333333 in the sample
> output come from? Is the sample inpus posted incomplete?
>
> I *think* that the query below will return the desired results. I didn't
> test is myself, since you didn't post the sample data in the form of
> INSERT statements and I'm too lazy to type them myself. <g>
>
> SELECT CASE WHEN agcprefix = 'DAC'
> THEN sysid
> ELSE ''
> END AS "Central Host",
> CASE WHEN agcprefix = 'DAC'
> THEN ''
> ELSE 'sysid'
> END AS "OffBoard",
> release AS "Release Level",
> platform AS "Platformm Type"
> FROM (SELECT DISTINCT sysid, agcprefix, release, platform
> FROM DsdaSysConfigProfile
> WHERE release LIKE 'DK%') AS D
>
> --
> Hugo Kornelis, SQL Server MVP
>
| |
| Hugo Kornelis 2006-10-24, 6:49 pm |
| On Mon, 2 Oct 2006 06:46:02 -0700, madgame wrote:
> I seen mention of INSERT, but there's no
>example so I'm not sure how to go about that. Can you give me an example of
>how to use INSERT to post data which would be more helpful for you, to help
>me? Thanks again.
Hi Madgame,
Sure:
INSERT INTO SomeTable (Column1, Column2)
SELECT 1, 'First row'
UNION ALL
SELECT 2, 'Second row'
UNION ALL
SELECT 3, 'Third row'
Replace table and columns names with names from your table, then use
apropriate data after each SELECT keyword.
Or as an alternative, use Vyas's script to generate INSERT statements
from the data in your DB: http://vyaskn.tripod.com/code.htm#inserts. But
this will only work if you already have a good set of test data in your
database.
BTW, did the quey I posted work for you?
--
Hugo Kornelis, SQL Server MVP
| |
| madgame 2006-10-24, 6:49 pm |
| Hugo,
The query did not work as I need it to. I will try and give a better
description of what's been asked along with the data from INSERT. Please
stay tuned...
Thanks again for taking the time to help.
> Hi Madgame,
>
> Sure:
>
> INSERT INTO SomeTable (Column1, Column2)
> SELECT 1, 'First row'
> UNION ALL
> SELECT 2, 'Second row'
> UNION ALL
> SELECT 3, 'Third row'
>
> Replace table and columns names with names from your table, then use
> apropriate data after each SELECT keyword.
>
> Or as an alternative, use Vyas's script to generate INSERT statements
> from the data in your DB: http://vyaskn.tripod.com/code.htm#inserts. But
> this will only work if you already have a good set of test data in your
> database.
>
> BTW, did the quey I posted work for you?
>
> --
> Hugo Kornelis, SQL Server MVP
>
| |
| madgame 2006-10-24, 6:49 pm |
| Hugo,
Here are the inserts.
INSERT INTO [mytable]
([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C111111','C222222'
,'DAC','DC041B','pla
tform2')
INSERT INTO [mytable]
([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C111111','C111111'
,'DAD','DK041B','pla
tform1')
INSERT INTO [mytable]
([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C111111','C222222'
,'DAD','DK041B','pla
tform2')
INSERT INTO [mytable]
([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C111111','C222222'
,'DAZ','DZ041B','pla
tform2')
INSERT INTO [mytable]
([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C111111','C222222'
,'DAP','DP041B','pla
tform2')
INSERT INTO [mytable]
([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C222222','C111111'
,'DAD','DK041B','pla
tform1')
INSERT INTO [mytable]
([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C222222','C222222'
,'DAP','DP041B','pla
tform2')
INSERT INTO [mytable]
([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C222222','C222222'
,'DAC','DC041B','pla
tform2')
INSERT INTO [mytable]
([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C222222','C222222'
,'DAD','DK041B','pla
tform2')
INSERT INTO [mytable]
([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C222222','C222222'
,'DAY','DY041B','pla
tform2')
INSERT INTO & #91;dsdasysconfigpro
file]
([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C333333','C333333'
,'DAD','DK061B','pla
tformtype2')
INSERT INTO & #91;dsdasysconfigpro
file]
([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C333333','C333333'
,'DAC','DC061B','pla
tformtype2')
INSERT INTO & #91;dsdasysconfigpro
file]
([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C333333','C333333'
,'DAX','DX061B','pla
tformtype2')
INSERT INTO & #91;dsdasysconfigpro
file]
([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C333333','C333333'
,'DAG','DG061B','pla
tformtype2')
INSERT INTO & #91;dsdasysconfigpro
file]
([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C444444','C444444'
,'DAD','DK050','plat
formtype3')
INSERT INTO & #91;dsdasysconfigpro
file]
([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C444444','C444444'
,'DAC','DC050','plat
formtype3')
INSERT INTO & #91;dsdasysconfigpro
file]
([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C444444','C444444'
,'DAX','DX050','plat
formtype3')
INSERT INTO & #91;dsdasysconfigpro
file]
([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C444444','C444444'
,'DAG','DG050','plat
formtype3')
The "Central Host" is the system whose agcprefix = DAC. Each "Central Host"
record should be a unique sysid. An offboard is considered a sysid which
does not have an agcprefix = DAC. An offboard always has a central host, but
a central host does not always have an offboard. Also he only wants the
release level from either type to be like 'DK%'.
He would like the central host row of information, followed by any offboards
for that central hosts with the central host field blank. If there are no
offboards then that field would be blank. He also wants totals as specified
below.
Sample output would look like this:
Central Host Offboard Release Platform
C222222 DK041B Platformtype2
C111111 DK041B Platformtype1
C333333 DK061B Platformtype2
C444444 DK050 Platformtype3
Totals:
Total Systems 4
Total offboards 1
Total Onboards 2
Total DK041B 2
Total DK061B 1
Total DK050 1
Total Platform1 1
Total Platform2 2
Total Platform3 1
"madgame" wrote:
[color=darkred]
> Hugo,
> The query did not work as I need it to. I will try and give a better
> description of what's been asked along with the data from INSERT. Please
> stay tuned...
>
> Thanks again for taking the time to help.
>
| |
| madgame 2006-10-24, 6:49 pm |
| dsdasysconfigprofile
= mytable
"madgame" wrote:
[color=darkred]
> Hugo,
> Here are the inserts.
>
> INSERT INTO [mytable]
> ([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C111111','C222222'
,'DAC','DC041B','pla
tform2')
> INSERT INTO [mytable]
> ([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C111111','C111111'
,'DAD','DK041B','pla
tform1')
> INSERT INTO [mytable]
> ([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C111111','C222222'
,'DAD','DK041B','pla
tform2')
> INSERT INTO [mytable]
> ([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C111111','C222222'
,'DAZ','DZ041B','pla
tform2')
> INSERT INTO [mytable]
> ([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C111111','C222222'
,'DAP','DP041B','pla
tform2')
> INSERT INTO [mytable]
> ([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C222222','C111111'
,'DAD','DK041B','pla
tform1')
> INSERT INTO [mytable]
> ([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C222222','C222222'
,'DAP','DP041B','pla
tform2')
> INSERT INTO [mytable]
> ([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C222222','C222222'
,'DAC','DC041B','pla
tform2')
> INSERT INTO [mytable]
> ([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C222222','C222222'
,'DAD','DK041B','pla
tform2')
> INSERT INTO [mytable]
> ([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C222222','C222222'
,'DAY','DY041B','pla
tform2')
> INSERT INTO & #91;dsdasysconfigpro
file]
> ([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C333333','C333333'
,'DAD','DK061B','pla
tformtype2')
> INSERT INTO & #91;dsdasysconfigpro
file]
> ([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C333333','C333333'
,'DAC','DC061B','pla
tformtype2')
> INSERT INTO & #91;dsdasysconfigpro
file]
> ([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C333333','C333333'
,'DAX','DX061B','pla
tformtype2')
> INSERT INTO & #91;dsdasysconfigpro
file]
> ([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C333333','C333333'
,'DAG','DG061B','pla
tformtype2')
> INSERT INTO & #91;dsdasysconfigpro
file]
> ([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C444444','C444444'
,'DAD','DK050','plat
formtype3')
> INSERT INTO & #91;dsdasysconfigpro
file]
> ([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C444444','C444444'
,'DAC','DC050','plat
formtype3')
> INSERT INTO & #91;dsdasysconfigpro
file]
> ([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C444444','C444444'
,'DAX','DX050','plat
formtype3')
> INSERT INTO & #91;dsdasysconfigpro
file]
> ([serial],[sysid],[agcprefix],[release],& #91;platform])VALUES
('C444444','C444444'
,'DAG','DG050','plat
formtype3')
>
> The "Central Host" is the system whose agcprefix = DAC. Each "Central Host"
> record should be a unique sysid. An offboard is considered a sysid which
> does not have an agcprefix = DAC. An offboard always has a central host, but
> a central host does not always have an offboard. Also he only wants the
> release level from either type to be like 'DK%'.
>
> He would like the central host row of information, followed by any offboards
> for that central hosts with the central host field blank. If there are no
> offboards then that field would be blank. He also wants totals as specified
> below.
>
> Sample output would look like this:
>
> Central Host Offboard Release Platform
> C222222 DK041B Platformtype2
> C111111 DK041B Platformtype1
> C333333 DK061B Platformtype2
> C444444 DK050 Platformtype3
>
> Totals:
> Total Systems 4
> Total offboards 1
> Total Onboards 2
> Total DK041B 2
> Total DK061B 1
> Total DK050 1
> Total Platform1 1
> Total Platform2 2
> Total Platform3 1
>
> "madgame" wrote:
>
| |
| Hugo Kornelis 2006-10-24, 6:49 pm |
| On Tue, 3 Oct 2006 07:56:03 -0700, madgame wrote:
>Hugo,
>Here are the inserts.
(snip)
>The "Central Host" is the system whose agcprefix = DAC. Each "Central Host"
>record should be a unique sysid. An offboard is considered a sysid which
>does not have an agcprefix = DAC. An offboard always has a central host, but
>a central host does not always have an offboard. Also he only wants the
>release level from either type to be like 'DK%'.
Hi Madgame,
Finally found some time to play with your data - thanks for posting it.
You write that you only want to include rows with release level like
'DK%'. After applying that filter, I see only rows with agcprefix other
than DAC. That means that, if I understand your requirements correct,
there is no "Central Host"- only offboards. But you als write that an
offboard must "have" a central host - whatever "have" means in this
context. And you provide sample output that lists three central hosts
and one offboard. So I guess I DON'T understand the requirements
correctly. Could you try to clarify this a bit further?
(snip)
>Totals:
>Total Systems 4
>Total offboards 1
>Total Onboards 2
>Total DK041B 2
>Total DK061B 1
>Total DK050 1
>Total Platform1 1
>Total Platform2 2
>Total Platform3 1
It's almost certainly a better idea to calculate these totals in the
front-end. You'll have to pass over the output from the query row by row
anyway - while doing that, you can keep track of the various totals
listed here, then display them when the last row of the query has been
processed. Let me know if you really need to get this part from SQL as
well - but note that performance will probably suck.
Oh, and by the way - are you using SQL Server 2000 or 2005?
--
Hugo Kornelis, SQL Server MVP
| |
| madgame 2006-10-24, 6:49 pm |
| Hugo,
Thanks for the help. I'm going to see if I can get assistance from someone
more familiar with the database whose sql skills are better than mine.
Thanks again for your assistance.
"Hugo Kornelis" wrote:
> On Tue, 3 Oct 2006 07:56:03 -0700, madgame wrote:
>
> (snip)
>
> Hi Madgame,
>
> Finally found some time to play with your data - thanks for posting it.
>
> You write that you only want to include rows with release level like
> 'DK%'. After applying that filter, I see only rows with agcprefix other
> than DAC. That means that, if I understand your requirements correct,
> there is no "Central Host"- only offboards. But you als write that an
> offboard must "have" a central host - whatever "have" means in this
> context. And you provide sample output that lists three central hosts
> and one offboard. So I guess I DON'T understand the requirements
> correctly. Could you try to clarify this a bit further?
>
> (snip)
>
> It's almost certainly a better idea to calculate these totals in the
> front-end. You'll have to pass over the output from the query row by row
> anyway - while doing that, you can keep track of the various totals
> listed here, then display them when the last row of the query has been
> processed. Let me know if you really need to get this part from SQL as
> well - but note that performance will probably suck.
>
> Oh, and by the way - are you using SQL Server 2000 or 2005?
>
> --
> Hugo Kornelis, SQL Server MVP
>
|
|
|
|
|