Re: sql query single table

dsdasysconfigprofile = mytable

"madgame" wrote:

Hugo,
Here are the inserts.

INSERT INTO [mytable]
([serial],[sysid],[agcprefix],[release],[platform])VALUES('C111111','C222222','DAC','DC041B','platform2')
INSERT INTO [mytable]
([serial],[sysid],[agcprefix],[release],[platform])VALUES('C111111','C111111','DAD','DK041B','platform1')
INSERT INTO [mytable]
([serial],[sysid],[agcprefix],[release],[platform])VALUES('C111111','C222222','DAD','DK041B','platform2')
INSERT INTO [mytable]
([serial],[sysid],[agcprefix],[release],[platform])VALUES('C111111','C222222','DAZ','DZ041B','platform2')
INSERT INTO [mytable]
([serial],[sysid],[agcprefix],[release],[platform])VALUES('C111111','C222222','DAP','DP041B','platform2')
INSERT INTO [mytable]
([serial],[sysid],[agcprefix],[release],[platform])VALUES('C222222','C111111','DAD','DK041B','platform1')
INSERT INTO [mytable]
([serial],[sysid],[agcprefix],[release],[platform])VALUES('C222222','C222222','DAP','DP041B','platform2')
INSERT INTO [mytable]
([serial],[sysid],[agcprefix],[release],[platform])VALUES('C222222','C222222','DAC','DC041B','platform2')
INSERT INTO [mytable]
([serial],[sysid],[agcprefix],[release],[platform])VALUES('C222222','C222222','DAD','DK041B','platform2')
INSERT INTO [mytable]
([serial],[sysid],[agcprefix],[release],[platform])VALUES('C222222','C222222','DAY','DY041B','platform2')
INSERT INTO [dsdasysconfigprofile]
([serial],[sysid],[agcprefix],[release],[platform])VALUES('C333333','C333333','DAD','DK061B','platformtype2')
INSERT INTO [dsdasysconfigprofile]
([serial],[sysid],[agcprefix],[release],[platform])VALUES('C333333','C333333','DAC','DC061B','platformtype2')
INSERT INTO [dsdasysconfigprofile]
([serial],[sysid],[agcprefix],[release],[platform])VALUES('C333333','C333333','DAX','DX061B','platformtype2')
INSERT INTO [dsdasysconfigprofile]
([serial],[sysid],[agcprefix],[release],[platform])VALUES('C333333','C333333','DAG','DG061B','platformtype2')
INSERT INTO [dsdasysconfigprofile]
([serial],[sysid],[agcprefix],[release],[platform])VALUES('C444444','C444444','DAD','DK050','platformtype3')
INSERT INTO [dsdasysconfigprofile]
([serial],[sysid],[agcprefix],[release],[platform])VALUES('C444444','C444444','DAC','DC050','platformtype3')
INSERT INTO [dsdasysconfigprofile]
([serial],[sysid],[agcprefix],[release],[platform])VALUES('C444444','C444444','DAX','DX050','platformtype3')
INSERT INTO [dsdasysconfigprofile]
([serial],[sysid],[agcprefix],[release],[platform])VALUES('C444444','C444444','DAG','DG050','platformtype3')

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,
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

.

Relevant Pages

  • Re: sql query single table
    ... INSERT INTO [dsdasysconfigprofile] ... An offboard always has a central host, ... a central host does not always have an offboard. ... He also wants totals as specified ...
    (microsoft.public.sqlserver.mseq)
  • sql query single table
    ... The requestor has specifics on the way it ... An offboard always has a central host, ... a central host does not always have an offboard. ... He also wants totals as specified ...
    (microsoft.public.sqlserver.mseq)
  • Re: sql query single table
    ... An offboard always has a central host, ... a central host does not always have an offboard. ... than DAC. ... It's almost certainly a better idea to calculate these totals in the ...
    (microsoft.public.sqlserver.mseq)