MS SQL Server :: Cross Tab?

T-sql Cross Tab

Jul 20, 2004

I should've posted this thread here....

http://www.dbforums.com/t1005070.html

Cross Tab

Jun 17, 2008

Is it possible change to cross tab using code?

Before code:

NAMETYPEVALUE
AAAHEIGHT66
AAAWEIGHT198

After code

NAMEHEIGHTWEIGHT
AAA66198

View 5 Replies View Related

Hi,
I'm just starting to learn SQL, just on the querying side and have what I think is quite a tricky function. It may be simple for you guys.

I want to make a report based on three tables. Enrolment, Course and Student. Its pretty easy until I need to crosstab it.

It's more complex than this (more fields but this gives the gist) Basically I want AcademicYearId, StudentID, coursecode, completionstatusid which would give the following.

AcademicYearId StudentId coursecode completionstatusid
05/06 912345 FWFT1 CONTINUING
05/06 914587 FWFT1 CONTINUING
05/06 987654 FWFT1 WITHDRAWN
05/06 954376 FWFT1 CONTINUING
05/06 934512 FWFT1 TRANSFERRED
05/06 923000 FWFT1 TRANSFERRED
04/05 945109 FWFT1 COMPLETED

I have managed to group it by academicyearid, coursecode and completionstatusid and done the counts ok but I want to twist the completionstatusid so each type of status has its own column like this

Year CourseCode Students Continuing Withdrawn Transferred Completed
05/06 FWFT1 6 3 1 2 0
04/05 FWFT1 1 0 0 0 1

Can anyone help me with the the extra columns please.

View 12 Replies View Related

Cross Tab??

Jul 20, 2005

thought my question was misinterpreted.actual table can be smaller or larger. but,within a procedure need to turn a created #temptablesimilar to(examid is in asc order):(id, p/f, examid, examname)1 f xxxx xxxxx1 f xxxx xxxxx1 f xxxx xxxxx2 p3 p4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx(note, id = 4 has 7 failed exams).to a table with up to five columns of failedexamnames:1 f xxxxx xxxxx xxxxx2 p2 p4 f xxxxx xxxxx xxxxx xxxxx xxxxxcan get rid of exameid if helps.--Sent by ricksql from yahoo subpart from comThis is a spam protected message. Please answer with reference header.Posted via http://www.usenet-replayer.com/cgi/content/new

View 2 Replies View Related

Cross Tab Query

Sep 7, 2007

Hi!
I m using sql 2005.I have a table as showing below.

year
Region
loan_amt
pur_id
purpose

1981
Andhra pradesh
$20,000.00
     1
Animal Husbandary

1981
Arunachal Pradesh
$110,000.00
     1
Animal Husbandary

1981
Assam
$240,000.00
     1
Animal Husbandary

1981
Bihar
$75,000.00
     1
Animal Husbandary

1981
Chhattisgarh
$55,500.00
     1
Animal Husbandary

1981
Gujarat
$77,500.00
     1
Animal Husbandary

1982
Goa
$44,888.00
     1
Animal Husbandary

1982
Himachal pradesh
$4,000.00
     1
Animal Husbandary

1982
Himachal pradesh
$20,000.00
     1
Animal Husbandary

1982
Jammu and kashmir
$30,000.00
     1
Animal Husbandary

1882
Jharkhand
$35,000.00
     1
Animal Husbandary

1982
Karnataka
$40,000.00
     1
Animal Husbandary

1982
Kerala
   $20,000.00
     1
Animal Husbandary

1982
Madhya pradesh
$5,000.00
     1
Animal Husbandary

I want to produce report as by using crosstab query as showing bellow

Year     Asam     Hyadrabad   goa arunachal pardesh etc............
1981      1000.00    2000.00       8000.2 00000        000000 ......
1981
'
'
Is it possible by crosstab query ?or please suggest me another way as early as possible.

Thanx in advance.
Abhishek

View 6 Replies View Related

Hi Need Help In Cross Tab Query

Apr 14, 2008

I wanna develope a report using cross tab query. I have table wit the name spend in which i have spenddate and there is also a amount field which will show the sum of amount field against months, i wanna develop repport in this format
merchant name jan 2007 feb 2007 dec 2007 jan 2008 feb2008
test 45 50 25 10 11

Please can any one help me how to do this this is very urgent ?

View 2 Replies View Related

Cross Tab Problem

Oct 14, 2004

I'm trying to run the cross tab query so that it returns only one row. If there is data I want to dump in the field value (fText), otherwise I put in a dash. The problem I'm having is that it's returning multiple rows when I want a single one. I could create a # table and dump the data in, but it seems a bit long winded.

Many thanks.

SELECT distinct (CASE WHEN f.TypeID=1 THEN 1 ELSE 0 END) f1,
(CASE WHEN f.TypeID=2 THEN f.fText else '-' END) f2,
(CASE WHEN f.TypeID=3 THEN f.fText else '-' END) f3,
(CASE WHEN f.TypeID=4 THEN f.fText else '-' END) f4,
(CASE WHEN f.TypeID=5 THEN f.fText else '-' END) f5,
(CASE WHEN f.TypeID=6 THEN f.fText else '-' END) f6,
(CASE WHEN f.TypeID=7 THEN f.fText else '-' END) f7,
(CASE WHEN f.TypeID=8 THEN f.fText else '-' END) f8,
(CASE WHEN f.TypeID=9 THEN f.fText else '-' END) f9,
(CASE WHEN f.TypeID=10 THEN f.fText else '-' END) f10
FROM tblTest f WHERE (f.fID=219)

View 1 Replies View Related

Cross Database

May 23, 2005

" I have two sql server2000 database named db1 and db2.
i have a user named 'user1' who has permission in both database.I have used a 'Select * from tableOne'in db1 when i have this table 'tableOne'in db1.
now this table was droped and created in db2.

what i need is i should log in to db1 and access the same select statement which is there in application used by my clients.

i have created a view in db1 with the same name as

'create view tableOne as select * from db2..tableOne'

now i can access.
Is there some othere way with out creating view?

View 7 Replies View Related

Reverse Cross Tab

Feb 19, 2002

Is is possible to write a query that does the reverse of a cross tab query, I have a table that is mirrored for our Main frame, the table contains a item number a warehouse number and a date field and then 43 fields like field01 thru field43 which store quantities, each field out from 01 is another date our from the date field and I need to look for the item, warehouse and return a date where one of the many field is greater than or equal to the quantity I am looking for, I obviously will have to calculate the date field but thought If I could do the reverse of a cross tab giving a resuld set of 43 records for everyone in the first file I could do a quick update for item number, warehouse match and quantity field greater than what i need returning the date

blah blah blah , sorry I made it sound so confusing

THANKS IN ADVANCE

View 2 Replies View Related

Oposite To Cross Tab?

Jun 15, 2004

I need to format the table shown :

Estimate | Quantity | Admin_can | Amind_am | Manu_fix | Manu_var
-------------------------------------------------------------------
5 | 1000 | .10 | 0.10 | 0.75 | 0.7

as the following :

Estimate | Variable | Quantity | Value | Total
---------------------------------------------------
5 | Admin_ca | 1000 | 0.1 | 100
5 | Admin_am | 1000 | 0.1 | 100
5 | Manu_fix | 1000 | 0.75 | 750
5 | Manu_var | 1000 | 0.7 | 700

I need to do this so it would be the correct format for a report. Can anyone suggest how to do this? I need to create a view with this format, so speed is
probably an issue?

Mike B

View 1 Replies View Related

Cross-Tab Vs SQL Query

Sep 15, 2006

Hi,
I have a report like this....

Date Sam Tom Harry 2006 2005
02/01/2006 4 1 1 6 2
02/02/2006 3 1 1 4 5
02/03/2006 2 2 0 4 4
-------
-------

02/12/2006 1 1 1 3 1
------------------------------------
Total -- -- -- -- --

Now I have all the data required for that in one table.

CREATE TABLE t1(
indate DATETIME
,agname VARCHAR(60)
,Polnumber VARCHAR(20)
)

So I have 2 options :
I have to do in in Cross tab or I have to create a query for this.
Is it possible to built a query like this,I mean assigning data as column?

Any help will be greatly appreciated.
Thanks!!

View 2 Replies View Related

Cross-tab Query

Jan 2, 2007

Hi all.

we have a department that has an access database with a bunch of queries in it. They want us to convert it to sql server. One of the queries is a cross-tab query. Is there an easy way to create this in sql? the column headings are the value of column from a table. This could change each month that they run it. How do I make the column heading a variable? I'm guessing a stored procedure would be best. Does anyone have any suggestions?

Thanks so much.
ODaniels

View 6 Replies View Related

Cross Tab Report

Dec 11, 2007

Using SQL Server 2005. Is there a way to create a cross tab report and then save it into a table only using SQL Code. Thank you for your help. David

View 5 Replies View Related

Can I Use PIVOT / Cross Tab For This? If Not...?

May 8, 2008

I have the following data structure (simplified)

declare @log table (
date smalldatetime,
category char(3),
value1 int,
value2 int)

insert into @log(date, category, value1, value2)
select '2008-01-01', 'ABC', 11,12 union all
select '2008-01-02', 'ABC', 35,53 union all
select '2008-01-03', 'ABC', 38,62 union all
select '2008-01-05', 'ABC', 59,95 union all
select '2008-01-02', 'XYZ', 42,21 union all
select '2008-01-04', 'XYZ', 9,7 union all
select '2008-01-05', 'XYZ', 89,45 union all
select '2008-01-01', 'HHH', 70,52 union all
select '2008-01-03', 'HHH', 3,83 union all
select '2008-01-05', 'HHH', 26,77

where
1) date is always up to the day (no time variation)
2) date and category can be considered a composite unique key

Given a date range (let's say, from 2008-01-01 to 2008-01-05) I need to get the below:

date abc_value1 abc_value2 xyz_value1 xyz_value2 hhh_value1 hhh_value2
---------- ----------- ----------- ----------- ----------- ----------- -----------
01/01/2008 11 12 NULL NULL 70 52
01/02/2008 35 53 42 21 NULL NULL
01/03/2008 38 62 NULL NULL 3 83
01/04/2008 NULL NULL 9 7 NULL NULL
01/05/2008 59 95 89 45 26 77

Ideally, the results include
- every day in the date range (even if there is no corresponding data for that date)
- the columns values to be dependent on the categories found within the date range

I came up with this

-- to fulfill requirement "every day in the date range"
declare @dt table (d smalldatetime)
insert into @dt
select '2008-01-01' union all
select '2008-01-02' union all
select '2008-01-03' union all
select '2008-01-04' union all
select '2008-01-05'

-- to fulfill display all the categories (manually determined)
select convert(varchar(10),d,101) as date,
abc.value1 as abc_value1, abc.value2 as abc_value2,
xyz.value1 as xyz_value1, xyz.value2 as xyz_value2,
hhh.value1 as hhh_value1, hhh.value2 as hhh_value2
from @dt dt
left join @log abc on dt.d = abc.date and abc.category = 'ABC'
left join @log xyz on dt.d = xyz.date and xyz.category = 'XYZ'
left join @log hhh on dt.d = hhh.date and hhh.category = 'HHH'

just for the purpose of generating the end result example, but in a real life situation, both the date range and the categories that may fall within that date range... are dynamic. To make my head spin even more, I also suspect the issue of value2 AND value3 being pulled is making this one complicated statement.

Any ideas? Thoughts? Suggestions?

View 3 Replies View Related

Cross Apply

May 22, 2008

What is Cross Apply, when it will be used ?

View 2 Replies View Related

Cross Tab Formula

May 26, 2008

in front end application,iam using pivot table,there is no option to create the growth rate calcuation/formula in FE.

my table data consists like below:
country_name Revenue PERIOD_TYPE_OUT Amount_out
UK Solutions01 Apr 2007 To 31 Mar 200856.83000000
UK Solutions01 Apr 2006 To 31 Mar 2007116.07000000

while iam using the cross tab in front end application data view is coming as
country_name

01 Apr 2006 To 31 Mar 2007 01 Apr 2007 To 31 Mar 2008
solution solution
uk 116.07 56.83

Actual o/p should be:

01 Apr 2006 To 31 Mar 2007 01 Apr 2007 To 31 Mar 2008 Growth
solution solution
uk 116.07 56.83 -0.51

can anybody tell how to calcualate growth in stored procedure level formala for calculating growth rate
is :
(56.83-116.07)/116.07
([current year]-[previous year])/[previous year]

View 2 Replies View Related

Cross-Tab Question

May 29, 2008

Hi

I am using SQL server 2005 and want to write a cross-tab query. i have managed a basic one but can't seem to write a more complex one.
if i have a table with columns called id, person_id, field_description, and content. e.g. image that the two values i could have in field description are age and favourite_colour, and then the corrosponding content for these fields would be 56yrs and blue or something.

now i want column names as person_id, age and favourite_colour. and the actual data should be content.

Have i made sense? I am sure this must be simple, but can't figure the syntax

thanks

pete

View 4 Replies View Related

Cross Query

Oct 11, 2007

View 4 Replies View Related

Cross Tab Query

Jul 20, 2005

Hi - I have what I think is a "simple problem".We currently have a database table that stores ItemProperties byItemID, PropertyID and Value. (The PropertyID references another tablefor property names and types.)This ItemProperties table is indexed and provides a flexible way ofstoring our item metadata. However, I would now like to returnrecordsets to the application layer that list these properties incolumn fashion, grouped by ItemIDI have seen that most cross-tab queries examples assume numerical dataand are based around using SUM and the GROUP BY phrase. But our datais a mixture of string and numbers (of various formats) and so GROUPBY is not an obvious solution. I have tried using CASE in the selectlist but this returns one row for each property with one column havingthe correct value, and all the other colums are NULL. I cannot thinkof how to combine these into one full record!!I could achieve the desired resultset by using a SELECT sub-statementfor every column, but I was hoping there was a more efficient method.Can anyone offer advice on this? It would be most appreciated.Best,Bill

View 1 Replies View Related

Problem In A CROSS To SQL

Sep 8, 2006

Dear friends;

I have a CROSS table in ACCESS that I want to export to SQL 2005. The query is:
TRANSFORM Count(GD_PROFILE.cnRow) AS CountOfcnRow
SELECT GD_PROFILE.ID
FROM GD_PROFILE
GROUP BY GD_PROFILE.ID
PIVOT GD_PROFILE.ID_APLIC;

The RESULT is:

ID

20

22

77

122

141

179

211

327

334

370

388

405

524

584

645

687

826

865

914

CN1

1

1

1

1

1

CN2

1

1

1

1

CN3

1

1

1

1

CN4

1

1

1

1

1

CN5

1

1

1

1

CN8

1

1

1

1

1

1

1

1

1

1

1

CN14

1

1

1

1

1

1

1

1

CN15

1

1

1

1

1

1

1

1

1

1

CN22

1

1

1

1

1

1

1

1

CN29

1

1

1

1

1

1

1

CN36

1

1

1

I want to create a query in SQL Server that give me rows from the cross query that are equal. For example, the row 1 and 4 are equal. Has the same values in each row. How can I make it programatically?

The Cross is a simple cross query, and the goal is return this final result:

Profile 1 - ID CN1, ID CN4
Profile 2 - ID CN2, ID CN3, ID CN5
Profile 3 - ID CN8
Profile 4 - ID CN14
Profile 5 - ID CN15
Profile 6 - ID CN22
Profile 7 - ID CN29
Profile 8 - ID CN36

Could you HELP me??
THANKS!!!!

View 5 Replies View Related

Cross Join

Dec 25, 2007

good morning
I wanna to put a sub query in the cross join like that

SELECT *

FROM table1

CROSS JOIN (select field

from table2

cross join table3)

but i got this error

Msg 102, Level 15, State 1, Line 7

Incorrect syntax near ')'.

can anyone help me!!!!

View 1 Replies View Related

Cross Tab Report

Apr 5, 2007

Hi,

I need some help on crosstab query. I have tried the examles, but somehow, not able to find a right solution.

Any help will be appreciated. The report I would like to have is the one at the bottom.

thx.

Josh

Table Structure>

Brand
Product
Line
Jan
Feb
Mar
Total

A
Chocolate
Volume
500
200
100
800

B
FizzDrink
Volume
250
300
200
750

A
Chocolate
Volume
230
100
120
450

A
Chocolate
Revenue
2,000
600
350
2,950

B
FizzDrink
Revenue
1,000
700
250
1,950

A
Chocolate
Revenue
800
400
550
1,750

A
Chocolate
AvgEarningPerProduct
4.00
3.00
3.50
3.69

B
FizzDrink
AvgEarningPerProduct
4.00
2.33
1.25
2.60

A
Chocolate
AvgEarningPerProduct
3.48
4.00
4.58
3.89

How the resultset should look like>

Brand
Product
Volume
Revenue
AvgEarningPerProduct
Month

A
Chocolate
500
2,000
4.00
Jan

A
Chocolate
200
600
3.00
Feb

A
Chocolate
100
350
3.50
Mar

A
Chocolate
800
2,950
3.69
Total

A
Chocolate
230
800
3.48
Jan

A
Chocolate
100
400
4.00
Feb

A
Chocolate
120
550
4.58
Mar

A
Chocolate
450
1,750
3.89
Total

B
FizzDrink
250
1,000
4.00
Jan

B
FizzDrink
300
700
2.33
Feb

B
FizzDrink
200
250
1.25
Mar

B
FizzDrink
750
1,950
2.60
Total

View 1 Replies View Related

Cross Table

Oct 5, 2006

Sir,

My query is

select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/11/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/12/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/13/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/14/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/15/2006'
group by task_date
union
select convert(datetime,task_date) as date,
sum(Case status_id when 1000 Then 7 else 0 end) as Programming,
sum(Case status_id when 1016 Then 5 else 0 end) as Design,
sum(Case status_id when 1752 Then 4 else 0 end) as Upload,
sum(Case status_id when 1032 Then 2 else 0 end) as Testing,
sum(Case status_id when 1128 Then 1 else 0 end) as Meeting,
sum(Case status_id when 1272 Then 1 else 0 end) as Others
from task_table where user_id='EMP10028' and task_date='9/16/2006'
group by task_date

My out put is
Date Program Design Upload Testing Meeting Others
2006-09-11 00:00:00.000 42 0 0 8 2 1
2006-09-12 00:00:00.000 77 0 0 4 0 0
2006-09-13 00:00:00.000 56 0 0 8 0 1
2006-09-14 00:00:00.000 63 0 0 6 0 1
2006-09-15 00:00:00.000 63 0 0 6 0 1

Now i want in below format
2006-09-11 2006-09-11 etc

Program 42 77

Design 0 0

Upload 0 0

Testing 8 4

Meeting 2 0

Others 1 0

Total 53 81

How to convert in this format .

View 3 Replies View Related

T-SQL Cross Tab /w Dates HELP!

Jan 16, 2008

I would like to be able to enter a date range and allow sql to format the result set as follows:

The two dates I would pass in are 7/1/2007 (start) to 12/31/2007 (end date)

I have three fields called:
Amount ($10.50)
Date (7/11/2007)
Key ( A, B, C, etc )

Table looks like this:
Key Amount Date
A 10.50 7/11/2007
C 1.00 8/1/2007
D 2.00 12/20/2007
B 3.45 8/5/2007

The result should look like this:
Key Jul07 Aug Sep Oct Nov Dec07 Total
A 10 40 0 0 5 10 65
B 30 10 10 5 10 10 75
Etc.

If I changed the date range to 1/1/2007 (start) to 12/31/2007 the result should look like this:
Key Jan07 Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec07 Total
A 4 10 42 0 0 3 10 40 0 0 5 10 124
B 1 6 10 0 2 0 30 10 10 5 10 10 94
Etc.

Thanks in advance.

View 5 Replies View Related

Cross Tab Query

Aug 7, 2006

Is there any simple cross tab query template, which can used by general user?

I need it so that I can provide it to our Power users, so that they can use it themselves, instead of coming back to the IT Department.

(something similar like the one found in the Access)

Tomal

View 6 Replies View Related

Cross-database References

Oct 22, 2007

Hi,
I'm doing a web application that will get some information from an ERP.
At this moment I have 2 databases:
1) The aspnetdb, where I have the tables for Merbership and Role
2) The ERP database
I need to put my web application tables on one of these two DB's. This tables will reference the users from the membership and some products from the ERP DB.
I will store products requests that will store both UserID (from aspnetdb) and ProductID (from ERP DB). I'm thinking to put these tables on the aspnetdb, so that all web application tables stick together. But, I will loose tha ability to make joins with the ERP database, right?
Do you think this will work? Can someone make some comments about this situation, and give me some tips?
Thank you!

View 6 Replies View Related

Cross Database Updates?

May 8, 2008

I am interested in adding a new row to a table 'Table05' that exists in a SQL Server 2005 database whenever a table 'Table00' in another SQL Server 2000 database has a row added to it. Can someone tell me a way to implement the above solution?

View 1 Replies View Related

Cross-database Query From ASP.NET

Feb 7, 2005

How do you write a SQL SELECT statement for a cross-database query in ASP.NET (ADO.NET). I understand the server.database.owner.table structure, but the command runs under a connection. How do I run a query under two connections?

View 1 Replies View Related

Cross Tab Stored Procedure

Jul 27, 2005

HiI have a table with rows of timestamped values for given variables. Each of the variables are periodically inserted at the same time. ieDateTime Variable Value.T1 V1 xxxxT1 V2 xxxxT1 V3 xxxxT2 V1 yyyyT2 V2 yyyyT2 V3 yyyyIn a SQLserver stored procedure I would like to insert this data into a temp table like the following. I don't need summaries just need a table or query where I can select on a variables value. ie "select * from temptable where V3 = yyyy"Datetime V1 V2 V3T1 xxxx xxxx xxxxT2 yyyy yyyy yyyy any suggestions for an SQL statement.thanks in advance.

View 2 Replies View Related

Complicated Cross-Tab Query

Feb 15, 2006

I have the following table and data:tblDepartments: (each department can only have a maximum of 3 sections attached to it)Columns: DepartmentName , SectionName Row1: dep1, sec1.0Row2: dep1, sec1.1Row3: dep1, sec1.2Row 4: dep2, sec2.0Row 5: dep3, sec3.0Row 6: dep3, sec3.1I need to derive the following table from tblDepartments :Columns: DepartmentName, SectionName1, SectionName2, SectionName3Row1: dep1, sec1.0, sec1.1, sec1.2Row2: dep2, sec2.0, '', ''Row3: dep3, sec3.0, sec3.1, ''Any ideas?

View 3 Replies View Related

How To Develop Cross-Tab Like Query

Sep 7, 1999

I have a table which stores the following data:

Col1 Col2
A Apple
A Orange
B Pear
C Banana
C Tomato
C Apple
C Pear

I want to display it in the following manner using a SELECT statement.
Col1 Col2 Col3 Col4 Col5
A Apple Orange
B Pear
C Banana Tomato Apple Pear

Can anyone help me figure out this SQL Statement.

Thanks,
Tim

View 2 Replies View Related

Information_shema Cross Server?

Oct 24, 2005

Hi All:

When I check out > 2000 customer databases cross 8 SQL 2000 servers, I wish I could use
"Select * from " + @server + "." + @databaseName + ".information_Schema.coulmns
where column_name = " + @column_name.

It did not works in this way.
However, why it works on
"Select * from serverA.master.information_schema.columns"?
It is also a cross server query but is on Master DB.

thanks
-D

View 3 Replies View Related