Re: sql query single table

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
.

Relevant Pages

  • Re: sql query single table
    ... Thanks for the help Hugo. ... record should be a unique sysid. ... An offboard always has a central host, ... a central host does not always have an offboard. ...
    (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)