Re: sql query single table

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:

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
    ... record should be a unique sysid. ... An offboard always has a central host, ... a central host does not always have an offboard. ... SELECT CASE WHEN agcprefix = 'DAC' ...
    (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)