Combining the content of two tables

Hi all,

How can I combine the contents of the two tables below? The combination result of these tables is provided below. Thanks

Table A

Client Weight Purchase

Tom 10 2

Bill 4 2

John 3 2

Table B

Client Weight Purchase

Jim 2 5

Lee 4 3

Bob 6 7

Combination table (result)

Client Weight Purchase

Tom 10 2

Bill 4 2

John 3 2

Jim 2 5

Lee 4 3

Bob 6 7

Give a look to the UNION and UNION ALL operators in books online. It should look something like this:

Code Snippet

select client,
weight,
purchase
from [table a]
union all -- or perhaps union
select client,
weight,
purchase
from [table b]

|||Works perfectly. Thanks.sqlsql

Combining dimensions in 1 hierarchy

Hi,

I have a cube that is migrated from AS 2000 to SSAS 2005.
One of the dimensions in the AS 2000 cube was a combination of 2 tables.
For instance:
Dimension name in AS 2000: Customer Type
Hierarchy: Level 1: Account Type (from table Dim_Account)
Lever 2: Product Type (from table Dim_Product)

Now after migrating this cube to SSAS 2005 this appears to produce an error.
I tried recreating this dimension, but it appears that I can't combine different dimension tables into 1 hierarchy. By this I don't mean the "Referenced" relationship type.

The only reference between the 2 dimension tables is the fact table.

I hope I make myself clear and someone can give me an answer, because otherwise I'm facing a lot of work :-/

Thanks!

It is bit unusual to build a single dimension from two tables that are referenced through the fact table.

There are several solutions you can try:

One, you can build 2 dimensions Account and Product and then stack hierarchy from Account on top of Product's hierarchy. This is probably better overall solution because you will be creating your dimensions and cube to follow the logical structure of the data you have in the relational database.

Another solution is to create Named Query in DSV to combine two tables into one and then build a single dimension on top of it. You'll have to make sure you do all the joins correctly.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

hello,

How do i stack those hierarchy?

thanks

Combining dimensions in 1 hierarchy

Hi,

I have a cube that is migrated from AS 2000 to SSAS 2005.
One of the dimensions in the AS 2000 cube was a combination of 2 tables.
For instance:
Dimension name in AS 2000: Customer Type
Hierarchy: Level 1: Account Type (from table Dim_Account)
Lever 2: Product Type (from table Dim_Product)

Now after migrating this cube to SSAS 2005 this appears to produce an error.
I tried recreating this dimension, but it appears that I can't combine different dimension tables into 1 hierarchy. By this I don't mean the "Referenced" relationship type.

The only reference between the 2 dimension tables is the fact table.

I hope I make myself clear and someone can give me an answer, because otherwise I'm facing a lot of work :-/

Thanks!

It is bit unusual to build a single dimension from two tables that are referenced through the fact table.

There are several solutions you can try:

One, you can build 2 dimensions Account and Product and then stack hierarchy from Account on top of Product's hierarchy. This is probably better overall solution because you will be creating your dimensions and cube to follow the logical structure of the data you have in the relational database.

Another solution is to create Named Query in DSV to combine two tables into one and then build a single dimension on top of it. You'll have to make sure you do all the joins correctly.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

hello,

How do i stack those hierarchy?

thanks

sqlsql

Combining a Line Graph and Scatter Graph

I have to make a report of a combination of salary trends of
employees. The idea is to show the salary of a employee for a certain
period of time, also showing where he stands when he compared to
salaries of other employees.
I will show the salary of the employee in a line Graph, thats fine I
have already done it. X axis -> Employment Time, Y-axis Salary The
qquery will be like
userid, date, salary
72, 01/01/2001, 1000
72, 06/01/2001, 1600
72, 01/01/2002, 6000
72, 06/01/2002, 7080
72, 01/01/2003, 8010
72, 06/01/2003, 10000
72, 01/01/2004, 10050
72, 06/01/2004, 15500
salary in the Values section
date in the Category Section
The Line Graph is done.
Assuming I have the same query with more userid values (For other
employees also). I have to plot scatter points around the Line Graph
showing the salaries of each other employee as against the employee
72.
So the Graph will have 1 line for userid 72, scatter points around
that line.
How can I do it. Working Ideas please.
Thanks !
Anand Sagar
Now I have to show scatter pointsanybody there !?

combine fields and text in select statement

Is it possible to combine fields and text in a select statement?

In a dropDownList I want to show a combination of two different fields, and have the value of the selected item come from a third field. So, I thought I could maybe do something like this:

SELECT DISTINCT GRPAS GroupName, "Year: " +YEAR + "Grade: " + GRDAS ShowMeFROM GE_DataWHERE (DIST = @.DIST)

I hoped that would take the values in YEAR and GRD and concatenate them with the other text. Then my dropDownList could show the ShowMe value and have the GroupName as the value it passes on. However, when I test this in the VS Query Builder, it says that Year and Grade are unknown column names and changes the double-quotes to square brackets.

If this is possible, or there's a better way to do it, I'd love some more info.

Thanks!

-Mathminded

You could do it in the SELECT statement. This kind of formatting is generally done at the application/GUI layer. You need to use single quotes for strings. Also Year is a keyword so you use square brackets.

SELECT DISTINCT GRPAS GroupName, 'Year: ' + [YEAR] + 'Grade: ' + [GRD]AS ShowMeFROM GE_DataWHERE (DIST = @.DIST)

|||

I got it to work! On a whim I decided to try single quotes and that got me farther. The error it produced then led me to this page:

http://weblogs.foxite.com/andykramek/archive/2005/09/18/921.aspx

Then I realized I needed to change the type for one of the columns. Thus, I ended up with this SQL statement which works:

SELECT DISTINCT GRPAS GroupName,'Year: ' +YEAR +' and Grade: ' +CAST(GRDAS CHAR(2))AS ShowMeFROM GE_DataWHERE (DIST = @.DIST)

|||

ndinakar:

You could do it in the SELECT statement. This kind of formatting is generally done at the application/GUI layer. You need to use single quotes for strings. Also Year is a keyword so you use square brackets.

SELECT DISTINCT GRPAS GroupName, 'Year: ' + [YEAR] + 'Grade: ' + [GRD]AS ShowMeFROM GE_DataWHERE (DIST = @.DIST)

Thanks, Dinakar! I thought I had figured it out quickly but you had it even faster! :-) Thanks for pointing out the keyword issue, also.

|||

The output from my working statement is:

GroupName ShowMe

AYear: 0203 and Grade: 3AYear: 0304 and Grade: 4AYear: 0405 and Grade: 5BYear: 0203 and Grade: 4BYear: 0304 and Grade: 5BYear: 0405 and Grade: 6CYear: 0203 and Grade: 5CYear: 0304 and Grade: 6CYear: 0405 and Grade: 7DYear: 0203 and Grade: 6DYear: 0304 and Grade: 7DYear: 0405 and Grade: 8EYear: 0203 and Grade: 7EYear: 0304 and Grade: 8EYear: 0405 and Grade: 9FYear: 0203 and Grade: 8FYear: 0304 and Grade: 9FYear: 0405 and Grade: 10GYear: 0203 and Grade: 9GYear: 0304 and Grade: 10GYear: 0405 and Grade: 11

With the way my dropDownList is working, the user could select any of the first 3 choices, for instance, and end up with the same group. It would really be great if I could get this to output a single GroupName and combine the other information. For instance:

GroupName ShowMe

A Years: 0203,0304,0405 and Grades: 3,4,5

B Years: 0203,0304,0405 and Grades 4,5,6

etc...

Would that be really difficult to do? I may play around with it and see if I can get it using embeded select statements. I've never tried those before. In the off chance that I'm successful, I'll post my results. :-)

|||Again, this is the task that has to be done in the application layer. You have more string functions available in .NET to manipulate the strings than in SQL Server.|||

I thought it may make things easier if I changed how the information was displayed. Rather than try to fit all that info into the dropDownList using some complicated SQL query, I'd like to create a table with columns Group, Years, and Grades so the users can refer to that when choosing just the group letter from the dropDownList. Here's a sample of the table I'd like to display to the users:

GROUP

YEARS

GRADES

A

0203, 0304, 0405

3, 4, 5

B

0203, 0304, 0405

4, 5, 6

C

0203, 0304, 0405

5, 6, 7

D

0203, 0304, 0405, 0506

6, 7, 8, 9

The database table has the data stored like this:

GRP

YEAR

GRD

A

0203

3

A

0304

4

A

0405

5

B

0203

4

B

0304

5

B

0405

6

C

0203

5

C

0304

6

C

0405

7

D

0203

6

D

0304

7

D

0405

8

D

0506

9

I've tried a bunch of different things with the GridView in Visual Studio but I'm not meeting with success. Any advice would be appreciated.

Thanks!

-Mathminded

|||

You can create a stored proc and a local table variable in it, get the values in the format you want into the table and do a select from the table at the end.

Check if this post helps in the concatenation:http://forums.asp.net/thread/1514443.aspx

Combination of Filegroup and Point in Time restore possible?

SQL2K5
Ive been doing some testing this morning and it appears that it is not
possible to combine PIT restores with Filegroup restores, is that correct? I
restore a Filegroup backup, and then the Trans Log backup, but there is no
option to stop at a specific time when doing this. It would sort of make
sense I suppose. It could be argued that doing so would leave the DB in an
inconsistent state if a table that resided outside of the restored Filegroup
had been updated before the restores began, but I just wanted to confirm my
findings(?).
I think the down side to this protection would be that if you are relying on
Filegroup backups/ restores, how would you recover a data loss that had
occured due to human error (someone deletes something they shouldn't have)?
After all, to bring the tables in the restored filegroup online I MUST take a
Trans Log backup and restore it, therefore my accidentally deleted data is
still not recovered.
Does this all sound right, or am I missing something here?
All insights are appreciated.Hi Chris
This sounds like your understanding is correct.
When doing a filegroup restore, SQL Server is assuming that other filegroups
are available, with transactions that might have occurred after the backup
of the filegroup that you are restoring. Restoring a FG requires that you
apply transaction logs to bring the FG up to the same point as the rest of
the database.
The point of FG restore is to recover from hardware errors on a single file
or fg. If you need to recover from user error, you'll need to restore the
whole db.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:9D74B6E1-CE97-4B5F-9E97-C0F946ECA811@.microsoft.com...
> SQL2K5
> Ive been doing some testing this morning and it appears that it is not
> possible to combine PIT restores with Filegroup restores, is that correct?
> I
> restore a Filegroup backup, and then the Trans Log backup, but there is no
> option to stop at a specific time when doing this. It would sort of make
> sense I suppose. It could be argued that doing so would leave the DB in an
> inconsistent state if a table that resided outside of the restored
> Filegroup
> had been updated before the restores began, but I just wanted to confirm
> my
> findings(?).
> I think the down side to this protection would be that if you are relying
> on
> Filegroup backups/ restores, how would you recover a data loss that had
> occured due to human error (someone deletes something they shouldn't
> have)?
> After all, to bring the tables in the restored filegroup online I MUST
> take a
> Trans Log backup and restore it, therefore my accidentally deleted data is
> still not recovered.
> Does this all sound right, or am I missing something here?
> All insights are appreciated.|||Thanks!
"Kalen Delaney" wrote:
> Hi Chris
> This sounds like your understanding is correct.
> When doing a filegroup restore, SQL Server is assuming that other filegroups
> are available, with transactions that might have occurred after the backup
> of the filegroup that you are restoring. Restoring a FG requires that you
> apply transaction logs to bring the FG up to the same point as the rest of
> the database.
> The point of FG restore is to recover from hardware errors on a single file
> or fg. If you need to recover from user error, you'll need to restore the
> whole db.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:9D74B6E1-CE97-4B5F-9E97-C0F946ECA811@.microsoft.com...
> > SQL2K5
> >
> > Ive been doing some testing this morning and it appears that it is not
> > possible to combine PIT restores with Filegroup restores, is that correct?
> > I
> > restore a Filegroup backup, and then the Trans Log backup, but there is no
> > option to stop at a specific time when doing this. It would sort of make
> > sense I suppose. It could be argued that doing so would leave the DB in an
> > inconsistent state if a table that resided outside of the restored
> > Filegroup
> > had been updated before the restores began, but I just wanted to confirm
> > my
> > findings(?).
> >
> > I think the down side to this protection would be that if you are relying
> > on
> > Filegroup backups/ restores, how would you recover a data loss that had
> > occured due to human error (someone deletes something they shouldn't
> > have)?
> > After all, to bring the tables in the restored filegroup online I MUST
> > take a
> > Trans Log backup and restore it, therefore my accidentally deleted data is
> > still not recovered.
> >
> > Does this all sound right, or am I missing something here?
> >
> > All insights are appreciated.
>
>

Combination of English and Chinese characters in Microsoft SQLServer 2000

Hello,
We have Microsoft SQL Server 2000 with Service Pack 3a installed. In
one of our databases, we have a table REQ with one of the fields
COMMENT being defined as TEXT. Now, when we try to view the COMMENT
field, it shows us only English characters whereas the Chinese
characters are displayed as ?.
Is it possible to store both English and Chinese characters in the same
column of a table? If yes, then how?
Thanks in advance.
New user.
It still does not work. Is there any other alternative?
On Nov 19, 1:48 am, "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sa...@.avtenta.si > wrote:
> --
> Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
|||newsqlserveruser (trupti.pujara@.gmail.com) writes:
> We have Microsoft SQL Server 2000 with Service Pack 3a installed. In
> one of our databases, we have a table REQ with one of the fields
> COMMENT being defined as TEXT. Now, when we try to view the COMMENT
> field, it shows us only English characters whereas the Chinese
> characters are displayed as ?.
> Is it possible to store both English and Chinese characters in the same
> column of a table? If yes, then how?
Yes, it's possible, but with the information you have given, it's difficult
to give exact advice.
First of all, which character set do you use for Chinese? Unicode? Big-5?
If you use Unicode, you should use ntext, if you use Big-5 or any other
non-Unicode character set, you should use text.
Next question is find out where things go wrong. Do something like:
SELECT substring(col, 1, 20), convert(binary(20), substring(col, 1, 20))
If there are question marks in the selected substring which should have
been Chinese characters, what do you see in their place in the binary
string? If you see 3F, the Chinese characters have been stored as ?, and
there is a problem on input. If you see the codes for the Chinese
characters, it's a display problem.
If it is an input problem, it would help to know who the characters enters
the database.
Disclaimer: I have no experience of working with Chinese on my own.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||I ran the query as u suggested and the output for the binary string
shows as 3F for the Chinese characters. If that means there is a
problem in input, how do I go ahead from here?
On Nov 21, 9:17 pm, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> newsqlserveruser (trupti.puj...@.gmail.com) writes:
> to give exact advice.
> First of all, which character set do you use for Chinese? Unicode? Big-5?
> If you use Unicode, you should use ntext, if you use Big-5 or any other
> non-Unicode character set, you should use text.
> Next question is find out where things go wrong. Do something like:
> SELECT substring(col, 1, 20), convert(binary(20), substring(col, 1, 20))
> If there are question marks in the selected substring which should have
> been Chinese characters, what do you see in their place in the binary
> string? If you see 3F, the Chinese characters have been stored as ?, and
> there is a problem on input. If you see the codes for the Chinese
> characters, it's a display problem.
> If it is an input problem, it would help to know who the characters enters
> the database.
> Disclaimer: I have no experience of working with Chinese on my own.
> --
> Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||newsqlserveruser (trupti.pujara@.gmail.com) writes:
> I ran the query as u suggested and the output for the binary string
> shows as 3F for the Chinese characters. If that means there is a
> problem in input, how do I go ahead from here?
To start with, please answer a few questions:
1) How does the data enter the database? From a Windows client? From a
web client? Which API do you use?
2) Which character set does the client use for the data?
3) What is the collation of the text column? (You can view this with
sp_help.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Thank you very much for all your help. The problem has been solved.
After changing the field from text to ntext in the database, the client
still had some problems. Even though East Asian languages were
supported, the Language for non-Unicode programs was English. When it
was changed to Chinese and the PC was rebooted, everything is all fine
now.
On Nov 26, 1:15 am, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> newsqlserveruser (trupti.puj...@.gmail.com) writes:
> 1) How does the data enter the database? From a Windows client? From a
> web client? Which API do you use?
> 2) Which character set does the client use for the data?
> 3) What is the collation of the text column? (You can view this with
> sp_help.)
> --
> Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Combination Graphs?

Are combination graphs possible in reporting services?yes, you can create a Column chart that includes a Line. There is an excellent paper on MSDN showing how|||

Thanks.

I think I phrased my question poorly.

I meant Bar-Chart with multiple Y-axis, like:

http://www.swiftchart.com/example_1.htm#mbar_ex1

Combination charts issues

I have created a chart to chart a combinational chart including a
stacked column and the continous line charts.
But the problem is this combinational chart nowhere looks like the
Excel combinational chart for the same data as in the reporting
services report. Not sure if this is a problem in the RS charting
engine.
How do I create a stacked column and a continous like chart.
Thanks
KarenHi Karen
So far, I didn't find anything where you can mix charttypes. The only thing
is, you can define a Series to be drawn as a line. You can set that in the
properties of a value on the second tab there's a checkbox for that.
HTH
Luzia
<karenmiddleol@.yahoo.com> schrieb im Newsbeitrag
news:1131399473.731448.325340@.g49g2000cwa.googlegroups.com...
>I have created a chart to chart a combinational chart including a
> stacked column and the continous line charts.
> But the problem is this combinational chart nowhere looks like the
> Excel combinational chart for the same data as in the reporting
> services report. Not sure if this is a problem in the RS charting
> engine.
> How do I create a stacked column and a continous like chart.
> Thanks
> Karen
>|||Hi Luzia
I even dropped setting a combination chart of line and stacked column
instead I set multiple series all line graphs. The charts generated are
nowhere comparable to what I get in Excel not sure what is wrong.
I am frustrated.
Thanks
Karen