combining select staements

Folks

I have three select statements. I want to display q_text based on
the respective where condition. How do i combine these three and write
as one select statement.

select q_text Questions from question
where new_account_flag = '1'

select q_text Questions from question
where disc_account_flag = '1'

select q_text Questions from question
where disc_account_flag = '0'

Remember that all the queries returns more than 1 value.

I tried to use
select (query1),
(query2),
(query3)
but because it is returning more than one value, there is error.

Can any suggest me any other syntax??

Thanksselect [q_text Questions] from question
where new_account_flag = '1'
UNION
select [q_text Questions] from question
where disc_account_flag = '1'
UNION
select [q_text Questions] from question
where disc_account_flag = '0'|||I think it should be UNION ALL, since either condition may produce duplicate results. UNION will eliminate duplicates.sqlsql

Combining Multiple rows into 1 row x 1 column

I have a table employee: that contains one column and three rows. How can I transform it using SELECT to display only one row and one column, with comma delimited strings: John, Mike, Dale?

Employee Name John Mike Dale

There are a number of ways to complete what you wish. Some features that you can take advantage of include:

select with CASE and MAX

User defined functions

SELECT with FOR XML syntax (better in SQL 2005 than SQL 2000)

PIVOT

Transact SQL SELECT extensions|||Very Cool, Thanks.

|||

Just for the sake of completeness, this can also be achieved via cursors:

Assuming #Employee temp table contains the data.

declare @.sql varchar(200), @.k int

set @.sql = ''

set @.k = 0

declare @.EmpName varchar(50)

declare abc cursor for select EmployeeName from #Employee

open abc

fetch next from abc into @.EmpName

while @.@.FETCH_STATUS = 0

begin

if @.k > 0 set @.SQL = @.SQL +', '

set @.SQL = @.SQL + @.EmpName

set @.k = @.k +1

fetch next from abc into @.EmpName

end

close abc

deallocate abc

SELECT @.SQL as Employees

Drop Table #Employee

|||

Code Snippet

declare @.Output varchar(max)

select @.Output = isnull(@.Output + ', ' + [Employee Name] , [Employee Name] )

from MyTable

select @.Output as [OneColumn]

combine OLTP with Star design

Hi,

We are working with AS for some time. We are using OWC as front end. I would like to display in the pivottable attributes of the dimension that are coming from the OLTP. For instance. I have a pivottable sales, customer by time. Next to the customer name I would like to have a column with his phone number and another with his address. Is something like that possible? [I am not concerned about the roll up - the information should only be displayed at leaf level]

To complicate the scenario, I would like to have an additional column called projection. Where the user can edit/add yearly projections for that customer. Any idea how to implement something like that?

Raphael

There are two problems you should recognize in here.

One is how to define your Analysis Services cube to include addtional information about customer phone nuber and address. For this, take a look at AdventureWorks sample project installed as part of SQL Server setup. Take a look at the Customer dimension and see how Phone and other attributes defined there.

Second, is the problem on how to display customer information in your application. BI Dev studio is using OWC as well. You can drag Customer dimension on Rows in cube browser. Drill down to the lowest level (Customer). Now right click on any member on the lowest level and select "Show properties in the report->Select All properties" and you will see all of the Customer's properties.

To be able to modify AS data directly in your application, it should support "writeback" feature. Basically it needs to know how to submit changes back to Analysis Serivices. Take a look if OWC supports writing data back to Analysis Services.

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

Combine multiple RDL files into one RDL file

Hello,

I need to generate a report, which should display 4 reports. Two tables and some charts. I have all these reports (I mean the .RDL files) individually. I can render the reports separately. But, now the need is to combine these reports in the one RDL file. Is this possible? If yes, how?

Also, I tried to create a stored procedure, which would call all these 4 SP inturn and provide 4 result sets. I thought of have an RDL by calling only this SP which would give 4 result sets. But infortunately, it gave only the first SP's result set. So, I have to combine the 4 RDL files into one to show on the Reporting Console. Can anyone please help me in this? Help would be grately appreciated.

Thanks a lot. Let me know if the question is not clear.

Mannu.

Well let's see.

You would need to create 4 datasets, one for each of the original reports. You can do this on the data tab by clicking the dataset drop down and Add new dataset.

Then, copy and paste each of the individual layouts into this "super" report.

On each of the tables and charts, go to the properties for the table or chart and select the appropriate dataset as the "dataset name" that you created.

|||

Yes GregSQL gives you right approach for the problem but there is a catch.

If you create 4 datasets using same SP that will still give you the first resultset. To overcome this issue I believe you have to pass an extra parameter say ReportType with datatype as Char. Give a condition in SP to return the Dataset you want to return for that perticular ReportType.

IN StoredProcedure:

IF @.ReportType = 'A'

BEGIN

YOUR DATASET

END

IF @.ReportType = 'B'

BEGIN

YOUR DATASET

END

Comming to report , click on each Dataset and go to Parameters section and give the ReportType value for each dataresult.'A' for First Dataset, 'B' for Second Datset.

I think this will complete the whole scenario...

Hope this helps

combine data in one row?

I need to display data in such a way that one row represents one Id. In
other words, I need to combine multiple rows data, separated by commas,
per each Id.
create table #temp
(a int,
b varchar(20))
insert into #temp values (1, 'green')
insert into #temp values (1, 'blue')
insert into #temp values (2, 'red')
insert into #temp values (3, 'black')
insert into #temp values (4, 'yellow')
insert into #temp values (4, 'white')
I need a query to give me this -
a b
-- --
1 green,blue
2 red
3 black
4 yellow, white
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***Check out the following thread:
http://groups.google.com/group/micr...4c4b0ff09ad4d58|||Thanks Jeff! I'll try to make it work in my case. However, I need to
make one change in the DDl. The ID fld is a varchar and it conatins
aplhanumeric values. see below the revised code:
create table #temp
(a varchar (20),
b varchar(20))
insert into #temp values ('1a', 'green')
insert into #temp values ('1a', 'blue')
insert into #temp values ('2v', 'red')
insert into #temp values ('3k', 'black')
insert into #temp values ('4x', 'yellow')
insert into #temp values ('4x', 'white')
I need a query to give me this -
a b
-- --
1a green,blue
2v red
3k black
4x yellow, white
Thanks for your help!!!
*** Sent via Developersdex http://www.examnotes.net ***|||You're kidding, right?
Just change the type of the ID column from int to varchar(20)|||Thanks, Jeff!
It is all working!
*** Sent via Developersdex http://www.examnotes.net ***

columns not displaying

i have made a mailing label report, that works just fine except that when selecting more than one column to display, i still only get one column. I went in to the Report properties, selected the layout tab, and made columns = 3. nothing happens. ive checked my margins and they all add up. i even reduced the columns to 2, but i still only see one column on the layout and preview tabs. select properties of the body, it says 3 columns (when i have 3 columns selected in the layout tab).

the width of the textbox is 2.5 inches. printing on a 8.5inch x 11inch sheet of paper.

why am i not getting more than one column?

what are you trying to display on each line

add1

add2

add3?

How are you building this, is each value held in a database returned by a dataset....?

It seems you may have all your data on one row or you are only returning on row (Is it in an array)

|||

Like wise for columns... sorry just realised you were talking about columns not rows...

Have you look to see if it is not displaying on a new page (How many pages has your report) and how is the data passed into the report?

|||

Hiya,

I am having exactly the same problem - did anyone ever figure it out? It shows the extra columns on the report in design mode, but doesnt seem to allow you to put anything on to them, so I assume it is going to mimic the content of the first column, which is fine, but when it renders, no extra columns are displayed, and it is no present on subsequent pages.

Any ideas?

Cheers!

columns not displaying

i have made a mailing label report, that works just fine except that when selecting more than one column to display, i still only get one column. I went in to the Report properties, selected the layout tab, and made columns = 3. nothing happens. ive checked my margins and they all add up. i even reduced the columns to 2, but i still only see one column on the layout and preview tabs. select properties of the body, it says 3 columns (when i have 3 columns selected in the layout tab).

the width of the textbox is 2.5 inches. printing on a 8.5inch x 11inch sheet of paper.

why am i not getting more than one column?

what are you trying to display on each line

add1

add2

add3?

How are you building this, is each value held in a database returned by a dataset....?

It seems you may have all your data on one row or you are only returning on row (Is it in an array)

|||

Like wise for columns... sorry just realised you were talking about columns not rows...

Have you look to see if it is not displaying on a new page (How many pages has your report) and how is the data passed into the report?

|||

Hiya,

I am having exactly the same problem - did anyone ever figure it out? It shows the extra columns on the report in design mode, but doesnt seem to allow you to put anything on to them, so I assume it is going to mimic the content of the first column, which is fine, but when it renders, no extra columns are displayed, and it is no present on subsequent pages.

Any ideas?

Cheers!

columns containing percantage values

Hi,
I am a newbie in reporting services with a pretty easy question.
How can display percentage values of a column depending of the column
sum?
Example:
_______________________________________________
row1 3 30%
row2 5 50%
row3 2 20%
_______________
sum 10 100%
_______________________________________________
How do I create the last column?
ThanxLast week I did this for the first time.
Created a sum total in a report footer for your 2nd col and the10 would show
for your sample. Give it a label like Total_Count. Then for the 3rd col
which is to be the percentage, use the expression =Sum(Fields!Total_Count.
Value)/(ReportItems!TotalCount.Value)
HTH!
James Ski
mickmack wrote:
>Hi,
>I am a newbie in reporting services with a pretty easy question.
>How can display percentage values of a column depending of the column
>sum?
>Example:
>_______________________________________________
>row1 3 30%
>row2 5 50%
>row3 2 20%
>_______________
>sum 10 100%
>_______________________________________________
>How do I create the last column?
>Thanx
--
Message posted via http://www.sqlmonster.com|||Thank you James for your prompt reply,
that works with one column.
But how would I create the following matrix?
_____________________________________
row1 3 30% 8 40%
row2 5 50% 8 40%
row3 2 20% 4 20%
_________________________
sum 10 100% 20 100%
_____________________________________
I dont know how to call the 10 and the 20 seperatly. Which names do
they have?
Thanx|||You could use the sum aggregate , but shouldn't the group % always be 100%..
You could also refer to the column with 10 in it using
ReportItems!textboxname.Value
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"mickmack" wrote:
> Thank you James for your prompt reply,
> that works with one column.
> But how would I create the following matrix?
> _____________________________________
> row1 3 30% 8 40%
> row2 5 50% 8 40%
> row3 2 20% 4 20%
> _________________________
> sum 10 100% 20 100%
> _____________________________________
> I dont know how to call the 10 and the 20 seperatly. Which names do
> they have?
> Thanx
>|||here is how I did:
drag a list into your report, in the list, drag in two subreports, one is
on the left and another one is on the right. then you write stored procudures
(name it sptest for now) with one parameter. In your subreport one, you call
sptest 0, and it will display the left 3 columns, subreport two, you call
sptest 1, and it will display the right 2 columns. sptest 0, and sptest 1 is
the dataset that you create when you design your subreport. The hard part for
this is the store procedure. you need to decide how many rows/columns that
you want to display. For my example, sptest 0, I display:
01/2004 5
02/2004 6
03/2004 7
.
.
.
sptest 1, then display on the second subreport
01/2005 1
02/2005 2
03/2005 3
.
.
.
finally, it ends up like this
01/2004 5 01/2005 1
02/2004 6 02/2005 2
03/2004 7 03/2005 3
. .
. .
. .
I hope this will give you some idea.
Henry
"mickmack" wrote:
> Thank you James for your prompt reply,
> that works with one column.
> But how would I create the following matrix?
> _____________________________________
> row1 3 30% 8 40%
> row2 5 50% 8 40%
> row3 2 20% 4 20%
> _________________________
> sum 10 100% 20 100%
> _____________________________________
> I dont know how to call the 10 and the 20 seperatly. Which names do
> they have?
> Thanx
>|||today I found a page at microsoft, where the problem is solved:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/semiadd2.asp

Column X is same, column Y is different?

Can someone point me in the right direction: How do I say: "Display all
rows from MYTABLE where column x is the same but column y is different"?
Thanks much.If I understand your question correctly then this will do it
select * from table where colX = someValue
and colY <> someValue
example
select * from employees where FirstName= 'John'
and Zipcode<> '10028'
http://sqlservercode.blogspot.com/|||SELECT a.*
FROM MYTABLE as a, MYTABLE as b
WHERE a.x = b.x AND a.y <> b.y
"Rick Charnes" wrote:

> Can someone point me in the right direction: How do I say: "Display all
> rows from MYTABLE where column x is the same but column y is different"?
> Thanks much.
>|||select distinct
x
,y
from table
?
DDL, sample data and expected results would help a lot.
ML
http://milambda.blogspot.com/|||I'm not sure what you mean.
Column X is the same as what?
Column Y is different than what?
Are you comparing rows against specific values?
Or are you comparing the rows against the rows from another table.
Can you give us a simple example?
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Rick Charnes" wrote:

> Can someone point me in the right direction: How do I say: "Display all
> rows from MYTABLE where column x is the same but column y is different"?
> Thanks much.
>|||Sorry; my fault: I don't know the values that I'm comparing. I want to
say: give me all rows in the table where col X is the same but within
those matched rows col Y is different. But I don't have any specific
values of X or Y that I'm comparing.
I think I need to use:
GROUP BY x
HAVING count(*) > 1
...and something with column Y <> column Y...?
somehow, but I'm not sure how.
In article <1136564368.957582.7870@.o13g2000cwo.googlegroups.com>,
denis.gobo@.gmail.com says...
> If I understand your question correctly then this will do it
> select * from table where colX = someValue
> and colY <> someValue
> example
> select * from employees where FirstName= 'John'
> and Zipcode<> '10028'
> http://sqlservercode.blogspot.com/
>|||Rick Charnes wrote:
> Sorry; my fault: I don't know the values that I'm comparing. I want to
> say: give me all rows in the table where col X is the same but within
> those matched rows col Y is different. But I don't have any specific
> values of X or Y that I'm comparing.
> I think I need to use:
> GROUP BY x
> HAVING count(*) > 1
> ...and something with column Y <> column Y...?
> somehow, but I'm not sure how.
> In article <1136564368.957582.7870@.o13g2000cwo.googlegroups.com>,
> denis.gobo@.gmail.com says...
Try:
SELECT T1.* -- List the column(s) don't use *
FROM your_table AS T1
WHERE EXISTS
(SELECT *
FROM your_table AS T2
WHERE T1.x = T2.x
AND T1.y <> T2.y);
Or maybe:
SELECT x
FROM your_table
GROUP BY x
HAVING MIN(y)<MAX(y);
depending on what column(s) you want to output.
David Portas
SQL Server MVP
--|||Here is one way
create table mark (value varchar(50),id int)
insert into mark
select 'AA',1 union all
select 'AA',1 union all
select 'AB',2 union all
select 'AB',1
select distinct m.* from mark m join(
select value from mark
group by id,value
having count(*) =1) m2 on m.value =m2.value
http://sqlservercode.blogspot.com/|||Post DDL, sample data and expected results, then.
ML
http://milambda.blogspot.com/|||This does it. Thanks very much.
In article <C8F7E55C-707D-43FA-B6ED-BC2697B8D64F@.microsoft.com>,
Lee@.discussions.microsoft.com says...
> SELECT a.*
> FROM MYTABLE as a, MYTABLE as b
> WHERE a.x = b.x AND a.y <> b.y
> "Rick Charnes" wrote:
>
>

Column Value Lookup

What is the best way to lookup a value in a column from another data set?
I am trying to locate and display the employee name based on the employee id
found in another data set.
I.E.
13 = Terry Ward
14 = Peter Jackson
Thank you.I might be wrong, but I don't think you can do that, unfortunately.
You need to make the EmployeeID a parameter that you can use to query your
employee database table for names.
What are you trying to do? Might be a different way of doing it.
Kaisa M. Lindahl Lervik
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> What is the best way to lookup a value in a column from another data set?
> I am trying to locate and display the employee name based on the employee
> id
> found in another data set.
> I.E.
> 13 = Terry Ward
> 14 = Peter Jackson
> Thank you.|||In the Task table, employees are identified as numeric values. References to
the actual employee names are found in the Employee table.
SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
dbo.tblIssue.Opened_By,
dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
dbo.tblIssue.Closed_Date
FROM dbo.tblIssue
INNER JOIN
dbo.tblEmployee ON dbo.tblIssue.Assigned_To =dbo.tblEmployee.Employee_ID
INNER JOIN
dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
"Kaisa M. Lindahl Lervik" wrote:
> I might be wrong, but I don't think you can do that, unfortunately.
> You need to make the EmployeeID a parameter that you can use to query your
> employee database table for names.
> What are you trying to do? Might be a different way of doing it.
> Kaisa M. Lindahl Lervik
> "Terry" <Terry@.discussions.microsoft.com> wrote in message
> news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> > What is the best way to lookup a value in a column from another data set?
> >
> > I am trying to locate and display the employee name based on the employee
> > id
> > found in another data set.
> >
> > I.E.
> > 13 = Terry Ward
> > 14 = Peter Jackson
> >
> > Thank you.
>
>|||Can you join the Task table to the emloyee table similar to the way the
tblIssue table is joined to the tblEmployee table is below to return the
employee name?
e.g. Something like this...
SELECT dbo.tblEmployee.Name
FROM tblTask
JOIN tblEmployee
ON tblTask.Employee_ID = tblEmployee.Employee_ID
"Terry" wrote:
> In the Task table, employees are identified as numeric values. References to
> the actual employee names are found in the Employee table.
> SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> dbo.tblIssue.Opened_By,
> dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> dbo.tblIssue.Closed_Date
> FROM dbo.tblIssue
> INNER JOIN
> dbo.tblEmployee ON dbo.tblIssue.Assigned_To => dbo.tblEmployee.Employee_ID
> INNER JOIN
> dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> "Kaisa M. Lindahl Lervik" wrote:
> > I might be wrong, but I don't think you can do that, unfortunately.
> > You need to make the EmployeeID a parameter that you can use to query your
> > employee database table for names.
> >
> > What are you trying to do? Might be a different way of doing it.
> >
> > Kaisa M. Lindahl Lervik
> > "Terry" <Terry@.discussions.microsoft.com> wrote in message
> > news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> > > What is the best way to lookup a value in a column from another data set?
> > >
> > > I am trying to locate and display the employee name based on the employee
> > > id
> > > found in another data set.
> > >
> > > I.E.
> > > 13 = Terry Ward
> > > 14 = Peter Jackson
> > >
> > > Thank you.
> >
> >
> >|||Do I need to create a new dataset and include the employee name in the report
in order to obtain the employee name along with the existing dataset called
IT_Projects?
DATASET 1:
SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
dbo.tblIssue.Opened_By,
dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
dbo.tblIssue.Closed_Date
FROM dbo.tblIssue
INNER JOIN
dbo.tblEmployee ON dbo.tblIssue.Assigned_To = dbo.tblEmployee.Employee_ID
INNER JOIN
dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
DATASET 2:
SELECT dbo.tblEmployee.Name
FROM tblTask
JOIN tblEmployee
ON tblTask.Employee_ID = tblEmployee.Employee_ID
"Matt" wrote:
> Can you join the Task table to the emloyee table similar to the way the
> tblIssue table is joined to the tblEmployee table is below to return the
> employee name?
> e.g. Something like this...
> SELECT dbo.tblEmployee.Name
> FROM tblTask
> JOIN tblEmployee
> ON tblTask.Employee_ID = tblEmployee.Employee_ID
> "Terry" wrote:
> > In the Task table, employees are identified as numeric values. References to
> > the actual employee names are found in the Employee table.
> >
> > SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> > dbo.tblIssue.Opened_By,
> > dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> > dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> > dbo.tblIssue.Closed_Date
> > FROM dbo.tblIssue
> > INNER JOIN
> > dbo.tblEmployee ON dbo.tblIssue.Assigned_To => > dbo.tblEmployee.Employee_ID
> > INNER JOIN
> > dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> >
> > "Kaisa M. Lindahl Lervik" wrote:
> >
> > > I might be wrong, but I don't think you can do that, unfortunately.
> > > You need to make the EmployeeID a parameter that you can use to query your
> > > employee database table for names.
> > >
> > > What are you trying to do? Might be a different way of doing it.
> > >
> > > Kaisa M. Lindahl Lervik
> > > "Terry" <Terry@.discussions.microsoft.com> wrote in message
> > > news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> > > > What is the best way to lookup a value in a column from another data set?
> > > >
> > > > I am trying to locate and display the employee name based on the employee
> > > > id
> > > > found in another data set.
> > > >
> > > > I.E.
> > > > 13 = Terry Ward
> > > > 14 = Peter Jackson
> > > >
> > > > Thank you.
> > >
> > >
> > >|||Ok, it sounds you are"...trying to locate and display the employee name based
on the employee id found in another data set."
So, it sounds like you have 1 dataset that does not return the employee name
and another dataset that does return the employee name. Instead of trying to
perform a lookup between the 2 datasets that you have, the best thing to do
would be to modify the query that currently does not include the employee
name to include the employee name in the select statement. Does that make
more sense?
If you want to post the 2 dataset queries that you have, that may help me
understand your situation better.
"Terry" wrote:
> Do I need to create a new dataset and include the employee name in the report
> in order to obtain the employee name along with the existing dataset called
> IT_Projects?
> DATASET 1:
> SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> dbo.tblIssue.Opened_By,
> dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> dbo.tblIssue.Closed_Date
> FROM dbo.tblIssue
> INNER JOIN
> dbo.tblEmployee ON dbo.tblIssue.Assigned_To => dbo.tblEmployee.Employee_ID
> INNER JOIN
> dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> DATASET 2:
> SELECT dbo.tblEmployee.Name
> FROM tblTask
> JOIN tblEmployee
> ON tblTask.Employee_ID = tblEmployee.Employee_ID
> "Matt" wrote:
> > Can you join the Task table to the emloyee table similar to the way the
> > tblIssue table is joined to the tblEmployee table is below to return the
> > employee name?
> >
> > e.g. Something like this...
> > SELECT dbo.tblEmployee.Name
> > FROM tblTask
> > JOIN tblEmployee
> > ON tblTask.Employee_ID = tblEmployee.Employee_ID
> >
> > "Terry" wrote:
> >
> > > In the Task table, employees are identified as numeric values. References to
> > > the actual employee names are found in the Employee table.
> > >
> > > SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> > > dbo.tblIssue.Opened_By,
> > > dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> > > dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> > > dbo.tblIssue.Closed_Date
> > > FROM dbo.tblIssue
> > > INNER JOIN
> > > dbo.tblEmployee ON dbo.tblIssue.Assigned_To => > > dbo.tblEmployee.Employee_ID
> > > INNER JOIN
> > > dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> > >
> > > "Kaisa M. Lindahl Lervik" wrote:
> > >
> > > > I might be wrong, but I don't think you can do that, unfortunately.
> > > > You need to make the EmployeeID a parameter that you can use to query your
> > > > employee database table for names.
> > > >
> > > > What are you trying to do? Might be a different way of doing it.
> > > >
> > > > Kaisa M. Lindahl Lervik
> > > > "Terry" <Terry@.discussions.microsoft.com> wrote in message
> > > > news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> > > > > What is the best way to lookup a value in a column from another data set?
> > > > >
> > > > > I am trying to locate and display the employee name based on the employee
> > > > > id
> > > > > found in another data set.
> > > > >
> > > > > I.E.
> > > > > 13 = Terry Ward
> > > > > 14 = Peter Jackson
> > > > >
> > > > > Thank you.
> > > >
> > > >
> > > >|||Thank you for your speedy response.
However, please review the following 2 dataset queries being used.
How can I include the employee name without causes JOIN conflicts?
DATASET 1:
SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
dbo.tblIssue.Opened_By,
dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
dbo.tblIssue.Closed_Date
FROM dbo.tblIssue
INNER JOIN
dbo.tblEmployee ON dbo.tblIssue.Assigned_To = dbo.tblEmployee.Employee_ID
INNER JOIN
dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
DATASET 2:
SELECT dbo.tblEmployee.Name
FROM tblTask
JOIN tblEmployee
ON tblTask.Employee_ID = tblEmployee.Employee_ID
"Matt" wrote:
> Ok, it sounds you are"...trying to locate and display the employee name based
> on the employee id found in another data set."
> So, it sounds like you have 1 dataset that does not return the employee name
> and another dataset that does return the employee name. Instead of trying to
> perform a lookup between the 2 datasets that you have, the best thing to do
> would be to modify the query that currently does not include the employee
> name to include the employee name in the select statement. Does that make
> more sense?
> If you want to post the 2 dataset queries that you have, that may help me
> understand your situation better.
> "Terry" wrote:
> > Do I need to create a new dataset and include the employee name in the report
> > in order to obtain the employee name along with the existing dataset called
> > IT_Projects?
> >
> > DATASET 1:
> >
> > SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> > dbo.tblIssue.Opened_By,
> > dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> > dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> > dbo.tblIssue.Closed_Date
> > FROM dbo.tblIssue
> > INNER JOIN
> > dbo.tblEmployee ON dbo.tblIssue.Assigned_To => > dbo.tblEmployee.Employee_ID
> > INNER JOIN
> > dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> >
> > DATASET 2:
> >
> > SELECT dbo.tblEmployee.Name
> > FROM tblTask
> > JOIN tblEmployee
> > ON tblTask.Employee_ID = tblEmployee.Employee_ID
> >
> > "Matt" wrote:
> >
> > > Can you join the Task table to the emloyee table similar to the way the
> > > tblIssue table is joined to the tblEmployee table is below to return the
> > > employee name?
> > >
> > > e.g. Something like this...
> > > SELECT dbo.tblEmployee.Name
> > > FROM tblTask
> > > JOIN tblEmployee
> > > ON tblTask.Employee_ID = tblEmployee.Employee_ID
> > >
> > > "Terry" wrote:
> > >
> > > > In the Task table, employees are identified as numeric values. References to
> > > > the actual employee names are found in the Employee table.
> > > >
> > > > SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> > > > dbo.tblIssue.Opened_By,
> > > > dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> > > > dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> > > > dbo.tblIssue.Closed_Date
> > > > FROM dbo.tblIssue
> > > > INNER JOIN
> > > > dbo.tblEmployee ON dbo.tblIssue.Assigned_To => > > > dbo.tblEmployee.Employee_ID
> > > > INNER JOIN
> > > > dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> > > >
> > > > "Kaisa M. Lindahl Lervik" wrote:
> > > >
> > > > > I might be wrong, but I don't think you can do that, unfortunately.
> > > > > You need to make the EmployeeID a parameter that you can use to query your
> > > > > employee database table for names.
> > > > >
> > > > > What are you trying to do? Might be a different way of doing it.
> > > > >
> > > > > Kaisa M. Lindahl Lervik
> > > > > "Terry" <Terry@.discussions.microsoft.com> wrote in message
> > > > > news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> > > > > > What is the best way to lookup a value in a column from another data set?
> > > > > >
> > > > > > I am trying to locate and display the employee name based on the employee
> > > > > > id
> > > > > > found in another data set.
> > > > > >
> > > > > > I.E.
> > > > > > 13 = Terry Ward
> > > > > > 14 = Peter Jackson
> > > > > >
> > > > > > Thank you.
> > > > >
> > > > >
> > > > >

Column name as the result of a query?

Simple example would look like that in MS SQL

SELECT 'a' AS (SELECT language_name FROM language WHERE language_id = 1)

So that the display is

English
a

as we assume that

SELECT language_name FROM language WHERE language_id = 1

returns only English

I have tried to that with a variable but it does not work

declare @.that as varchar(15);
set @.that = (select language_name_u from language where language_id = 1);
select 'a' as @.that

LOL

I just tried another way as i was going to post

declare @.that as varchar(15);
set @.that = (select language_name_u from language where language_id = 1);
select 'a' as "@.that"

and it worked!

Posting anyway so people might answer with a better solution with no variable

Thanks a lot

Mordandeclare @.that as varchar(15);
set @.that = (select language_name_u from language where language_id = 1);
select 'a'= @.that

column management help

I have to read 25 usernames from a single users row

than display to that user the 25 profiles.

I assume this is possible with a subquery right?

Now do i have to make 25 columns 1 for each username or

could it read user1 user2 user3 ? and how please.. Thanks much

Can you explain a little more about how these usernames are currentlystored in your database, and why you need to make them columns? Sounds like they're better suited to being rows, instead ofcolumns.
|||

i have an infinite number of rows with primary key username

each column needs to store up to 25 usernames who sent a message

than im thinking a subquery can display the info for each username for that exact person.

info to be display is picture and some things about themself

How... im still trying to figure out.

i have this in one table

Username - primary key

user1, user2 user3 as columns - for each person that made an action on that users page

store them in a column

My question is: There a simplier way to read these users instead of making a bunch of columns for each user who made an action?

|||

Guys would making subtables cause trouble in the long run?

If i make a sub table for every user?

|||

ck1mark wrote:

My question is: There a simplier way to readthese users instead of making a bunch of columns for each user who madean action?

Hi,
Yep, there is. Any time you have an inclination of creating a tablewith numbered fields like that, it is a huge warning flag that thestructure is not normalized. Almost certainly, a better way is to havea Message table (I'm guessing here on what table names will make sense)that has a MessageID and whatever other fields you need to have tostore whatever information.
Then you have a child table with a structure something like this:
UserID -- Could be the user name or a artificial primary key
MessageID -- A foreign key into the Message table
This way storage is more efficient, you don't have to worry about theone message you'll occasionally get that has 26 users, and SQL isdesigned to handle related tables like this.
Make sense?
Don
|||

help me write this please im having trouble

i need to insert the select statement values

INSERT INTO table1
VALUES username, photo1

(SELECT r.username, photo1
FROM table2 r, table3 p
WHERE r.username = '" & user.identity.name & "' AND r.username = p.username)

|||You might want to check Books Online for the syntax. But it should besomething like this (untested, so may still need some tweaking):

INSERT INTO table1 (username, photo1)
(SELECT r.username, photo1
FROM table2 r, table3 p
WHERE r.username = '" & user.identity.name & "' AND r.username = p.username)

I'm not quite sure what the context is for this statement, so the quotes might need more tweaking as well.
Does that work? If not, what troubles are you having?
By the way, this is dangerous code because of SQL injection. Usinguser.identity.name may be safe, but only if you've made sure the namedoesn't have any bad stuff in it. It's always better to useparameterized queries.
Don

Column Chart

Hi,
Is it possible to display the Category Fields diagonally ?
ThanksSorry, this is not supported.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"collie" <collie@.discussions.microsoft.com> wrote in message
news:2076A937-2128-4AF9-B77C-0D6ED687C7F2@.microsoft.com...
> Hi,
> Is it possible to display the Category Fields diagonally ?
> Thanks|||Thanks for the reply.
"Robert Bruckner [MSFT]" wrote:
> Sorry, this is not supported.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "collie" <collie@.discussions.microsoft.com> wrote in message
> news:2076A937-2128-4AF9-B77C-0D6ED687C7F2@.microsoft.com...
> > Hi,
> >
> > Is it possible to display the Category Fields diagonally ?
> >
> > Thanks
>
>