Data Extract Question

Hi, I'm hoping someone has an idea or two on this topic. Basically I have three tables of data say tContact, tQuestion, tAnswer
tContact
----
ContactID
Email
Name
tQuestion
----
QuestionID
Question
tAnswer
----
QuestionID
ContactID
Answer
I need to extract the data for the client and they would like to seethe data with one line per contact, but showing every answer to everyquestion... they would like the data formatted like this:
ContactID, Email, Name, Question1Answer, Question2Answer, Question3Answer, Question4Answer, etc......
Obviously to get the data I cansimply do an outerjoin to get allcontact data then all questions, and answers that exist... but thatwill obviously return tabular data with one row per eachanswer... Does anyone have any ideas on how to do this using justSQL? I can pull the data and write a function that spits it outto text using the Stringbuilder class and some logic, but I'm thinkingthis must be possible in SQL natively... any help would be more thanappreciated. Thanks in advance.
-e

emaxwell wrote:

Does anyone have any ideas on how to do this using justSQL? I can pull the data and write a function that spits it outto text using the Stringbuilder class and some logic, but I'm thinkingthis must be possible in SQL natively

Unfortunately SQL Server 2000 does not include the ability to nativelyperform pivot table/cross-tab queries. You will either need to doit in the presentation layer (see the link inthis post) or you can go through some gyrations in your T-SQL code (see this article:Dynamic Cross-Tabs/Pivot Tables).

data dictionary tool

Is there any tool for data dictionary? Basically there are no
descriptions/comments to many of the columns (about 99%) in the database.
Datawarehouse team does not know exactly what those columns are, where its
used, how its used. No comments have been defined in the system tables. We
would like to have a tool where SME's/Bussiness analysts enter the
description for the columns in the production database. We have SQL Server
databases, many oracle dbs, few sybase databases, ACCESS. Sometimes we might
have to search for strings in the descriptions entered.
Answered in another NG. Please do not post the same message independently
to multiple newsgroups.
sql

data dictionary tool

Is there any tool for data dictionary? Basically there are no
descriptions/comments to many of the columns (about 99%) in the database.
Datawarehouse team does not know exactly what those columns are, where its
used, how its used. No comments have been defined in the system tables. We
would like to have a tool where SME's/Bussiness analysts enter the
description for the columns in the production database. We have SQL Server
databases, many oracle dbs, few sybase databases, ACCESS. Sometimes we might
have to search for strings in the descriptions entered.Answered in another NG. Please do not post the same message independently
to multiple newsgroups.

data dictionary tool

Is there any tool for data dictionary? Basically there are no
descriptions/comments to many of the columns (about 99%) in the database.
Datawarehouse team does not know exactly what those columns are, where its
used, how its used. No comments have been defined in the system tables. We
would like to have a tool where SME's/Bussiness analysts enter the
description for the columns in the production database. We have SQL Server
databases, many oracle dbs, few sybase databases, ACCESS. Sometimes we might
have to search for strings in the descriptions entered.Answered in another NG. Please do not post the same message independently
to multiple newsgroups.

data cleansing and translation tools for relational databases

Hello All
We have a data migration project. This is basically what we plan to
achieve we have a legacy system which as some data with German long
texts like material texts,etc
We want to take data coming in flat files which as this german texts
and convert this to English text the texts are primarily products
names please advise what tools external or whatever is available to do
this translation to english the texts are about 15 to 20 characters
max.
Next we have data coming from legacy systems that we want to cleanse
like identify duplicates based on things like addresses:
eq: we might have a customer called ABC one at address 118 Main
Street, Nashua and another same customer defined in our another legacy
system called ABC Inc but address at Main Street, Nashua
We want a data cleansing tool or library that works on top of SQL
Server that can do this kind of data pattern identification,etc
Please advice free tools and tools within SQL if I am not aware of
that can do this as well good 3rd party tools that can do this.
Thanks
KarenHi
I don't know of any data cleansing tools that may perform all the functions
that you require. Even if you employed a company to manually clean the data
there is likely to be some degree of error at th e nd of the exercise.
I you carried out the changes yourself it may be possible to clean the data
using a translation table and/or ad-hoc queries.
John
"Karen Middleton" <karenmiddleol@.yahoo.com> wrote in message
news:a5fd468a.0409032125.35235ea@.posting.google.com...
> Hello All
> We have a data migration project. This is basically what we plan to
> achieve we have a legacy system which as some data with German long
> texts like material texts,etc
> We want to take data coming in flat files which as this german texts
> and convert this to English text the texts are primarily products
> names please advise what tools external or whatever is available to do
> this translation to english the texts are about 15 to 20 characters
> max.
> Next we have data coming from legacy systems that we want to cleanse
> like identify duplicates based on things like addresses:
> eq: we might have a customer called ABC one at address 118 Main
> Street, Nashua and another same customer defined in our another legacy
> system called ABC Inc but address at Main Street, Nashua
> We want a data cleansing tool or library that works on top of SQL
> Server that can do this kind of data pattern identification,etc
> Please advice free tools and tools within SQL if I am not aware of
> that can do this as well good 3rd party tools that can do this.
> Thanks
> Karen|||There are many companies providing software and services to help clean up
name and address databases. These methods are usually specific to particular
postal systems or geographical areas so I suggest you Google for something
that meets your requirements in the locales of interest to you.
--
David Portas
SQL Server MVP
--|||Try the following link :
http://www.itcg.nl/
From their site :
CLUE
Have you ever experienced problems with duplicate customers,
addresses, products or any other information?
CLUE®, the CLUster Engine from ITCG is a generic component that uses
fuzzy matching logic to match data that is nearly the same. As a generic
component CLUE® is useful in many ways.
Few years ago they were specialising in cleaning data and
merging data from several sources into one database.
They had some fancy algoritms to detect double entries in
the database. They were very convincing in their presentation,
but I have no experience with their range of products.
good luck,
ben brugman
"Karen Middleton" <karenmiddleol@.yahoo.com> wrote in message
news:a5fd468a.0409032125.35235ea@.posting.google.com...
> Hello All
> We have a data migration project. This is basically what we plan to
> achieve we have a legacy system which as some data with German long
> texts like material texts,etc
> We want to take data coming in flat files which as this german texts
> and convert this to English text the texts are primarily products
> names please advise what tools external or whatever is available to do
> this translation to english the texts are about 15 to 20 characters
> max.
> Next we have data coming from legacy systems that we want to cleanse
> like identify duplicates based on things like addresses:
> eq: we might have a customer called ABC one at address 118 Main
> Street, Nashua and another same customer defined in our another legacy
> system called ABC Inc but address at Main Street, Nashua
> We want a data cleansing tool or library that works on top of SQL
> Server that can do this kind of data pattern identification,etc
> Please advice free tools and tools within SQL if I am not aware of
> that can do this as well good 3rd party tools that can do this.
> Thanks
> Karen|||Another thing you might look at for address cleaning is CASS certification.
This is a standard set by the US Postal service, and there is much software
around to help identify bad addresses, but it does not address your other
data quality problems.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Karen Middleton" <karenmiddleol@.yahoo.com> wrote in message
news:a5fd468a.0409032125.35235ea@.posting.google.com...
> Hello All
> We have a data migration project. This is basically what we plan to
> achieve we have a legacy system which as some data with German long
> texts like material texts,etc
> We want to take data coming in flat files which as this german texts
> and convert this to English text the texts are primarily products
> names please advise what tools external or whatever is available to do
> this translation to english the texts are about 15 to 20 characters
> max.
> Next we have data coming from legacy systems that we want to cleanse
> like identify duplicates based on things like addresses:
> eq: we might have a customer called ABC one at address 118 Main
> Street, Nashua and another same customer defined in our another legacy
> system called ABC Inc but address at Main Street, Nashua
> We want a data cleansing tool or library that works on top of SQL
> Server that can do this kind of data pattern identification,etc
> Please advice free tools and tools within SQL if I am not aware of
> that can do this as well good 3rd party tools that can do this.
> Thanks
> Karen|||I would suggest calling a company like www.listadvantage.com and have
the names cleaned and cass certified.
they r self serve and very easy to work with
"ben brugman" <ben@.niethier.nl> wrote in message news:<OlCP4zAlEHA.536@.TK2MSFTNGP11.phx.gbl>...
> Try the following link :
> http://www.itcg.nl/
> From their site :
> CLUE
> Have you ever experienced problems with duplicate customers,
> addresses, products or any other information?
> CLUE®, the CLUster Engine from ITCG is a generic component that uses
> fuzzy matching logic to match data that is nearly the same. As a generic
> component CLUE® is useful in many ways.
>
> Few years ago they were specialising in cleaning data and
> merging data from several sources into one database.
> They had some fancy algoritms to detect double entries in
> the database. They were very convincing in their presentation,
> but I have no experience with their range of products.
> good luck,
> ben brugman
>
> "Karen Middleton" <karenmiddleol@.yahoo.com> wrote in message
> news:a5fd468a.0409032125.35235ea@.posting.google.com...
> > Hello All
> >
> > We have a data migration project. This is basically what we plan to
> > achieve we have a legacy system which as some data with German long
> > texts like material texts,etc
> >
> > We want to take data coming in flat files which as this german texts
> > and convert this to English text the texts are primarily products
> > names please advise what tools external or whatever is available to do
> > this translation to english the texts are about 15 to 20 characters
> > max.
> >
> > Next we have data coming from legacy systems that we want to cleanse
> > like identify duplicates based on things like addresses:
> >
> > eq: we might have a customer called ABC one at address 118 Main
> > Street, Nashua and another same customer defined in our another legacy
> > system called ABC Inc but address at Main Street, Nashua
> >
> > We want a data cleansing tool or library that works on top of SQL
> > Server that can do this kind of data pattern identification,etc
> >
> > Please advice free tools and tools within SQL if I am not aware of
> > that can do this as well good 3rd party tools that can do this.
> >
> > Thanks
> > Karen

Data by Row for a chart?

Hi,
I have a dataset which displayes data in a row (no of documents in each month)
So, basically I get 12 columns and one row with value for each month.
I want to create a Bar Chart, but can i supply the data by row?
I have to add 12 data fields to the Bar chart currently and they all show up
in different colors because of that. Is there a way for me to drop the row as
a series (like in excel?)
Thanks,Hi,
Can anyone please let me know if this is possible?
Along with the (unanswered) questions posted before, i also wanted to know
if it is possible to have one series as a line chart and one series as a Bar
chart on the same graph? if yes, can someone PLEASE tell me how? THANKS
"Prashant" wrote:
> Hi,
> I have a dataset which displayes data in a row (no of documents in each month)
> So, basically I get 12 columns and one row with value for each month.
> I want to create a Bar Chart, but can i supply the data by row?
> I have to add 12 data fields to the Bar chart currently and they all show up
> in different colors because of that. Is there a way for me to drop the row as
> a series (like in excel?)
> Thanks,
>|||If you have RS 2000 SP1 or SP2 installed, you could set the color to the
same identical value for all values (under appearance - series styles).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Prashant" <Prashant@.discussions.microsoft.com> wrote in message
news:1712D9E5-284D-40CF-8E17-B5BBE3476A5C@.microsoft.com...
> Hi,
> I have a dataset which displayes data in a row (no of documents in each
> month)
> So, basically I get 12 columns and one row with value for each month.
> I want to create a Bar Chart, but can i supply the data by row?
> I have to add 12 data fields to the Bar chart currently and they all show
> up
> in different colors because of that. Is there a way for me to drop the row
> as
> a series (like in excel?)
> Thanks,
>|||Yes, you can do that. Create your column chart. Then go to the chart
properties dialog. In the dialog go to the Data tab and edit the properties
of your trend data series. The "Edit chart value" dialog should pop up. In
this dialog go to the Appearance tab and select "Plot data as line".
You can also check this KB article: http://support.microsoft.com/kb/842422
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Prash" <Prash@.discussions.microsoft.com> wrote in message
news:C9D588CF-E695-4E62-9E49-4E4F9FEC0EAD@.microsoft.com...
> Hi,
> Can anyone please let me know if this is possible?
> Along with the (unanswered) questions posted before, i also wanted to know
> if it is possible to have one series as a line chart and one series as a
> Bar
> chart on the same graph? if yes, can someone PLEASE tell me how? THANKS
>
> "Prashant" wrote:
>> Hi,
>> I have a dataset which displayes data in a row (no of documents in each
>> month)
>> So, basically I get 12 columns and one row with value for each month.
>> I want to create a Bar Chart, but can i supply the data by row?
>> I have to add 12 data fields to the Bar chart currently and they all show
>> up
>> in different colors because of that. Is there a way for me to drop the
>> row as
>> a series (like in excel?)
>> Thanks,

Customizing Subscription Maintenance

I would like to use my own subscription setup screens, but am having a little
trouble. I basically need to be able to grab a report and schedule it. As
far as I can tell, I think I only need the following tables:
ReportServer.dbo.Schedule, ReportServer.dbo.Subscriptions,
ReportServer.dbo.ReportSchedule w/ the corresponding Stored Procs CreateTask,
CreateSubscription, AddReportSchedule, respectively. I also need the
following from msdb: sysjobs, sysjobsteps, sysjobschedules. I've worked w/
the msdb tables before and have automated the process of creating scheduled
jobs, but am a noob when trying to do the same w/ the ReportServer db tables.
Is there any documentation out there on customizing these schedules (e.g.,
how the scheduleid or subscriptionid is created)?Ok...I don't think that it is possible to get around this one. Anyone have
any luck / information on doing what I want (see previous post).
What about customizing the screens then? All I want the user to be able to
see and have the ability to do is create and modify subscriptions for reports.
"Neo" wrote:
> I would like to use my own subscription setup screens, but am having a little
> trouble. I basically need to be able to grab a report and schedule it. As
> far as I can tell, I think I only need the following tables:
> ReportServer.dbo.Schedule, ReportServer.dbo.Subscriptions,
> ReportServer.dbo.ReportSchedule w/ the corresponding Stored Procs CreateTask,
> CreateSubscription, AddReportSchedule, respectively. I also need the
> following from msdb: sysjobs, sysjobsteps, sysjobschedules. I've worked w/
> the msdb tables before and have automated the process of creating scheduled
> jobs, but am a noob when trying to do the same w/ the ReportServer db tables.
>
> Is there any documentation out there on customizing these schedules (e.g.,
> how the scheduleid or subscriptionid is created)?
>