Crystal Report : Cross Tab Formatting(Urgent)

Hi!I am new to cross tab in reports.I am using Seagate Crystal Report 7.Currently developing report with few tables in it. I am having problem grouping it into the fields that i want.I also wish to know how do i add formula in it..as well as writing queries in the access itself.I cant seems to do anything with the column/row on the report.I hope to have some guidelines.Thank You.Hi!I am new to cross tab in reports.I am using Seagate Crystal Report 7.Currently developing report with few tables in it. I am having problem grouping it into the fields that i want.I also wish to know how do i add formula in it..as well as writing queries in the access itself.I cant seems to do anything with the column/row on the report.I hope to have some guidelines.Thank You.

Hi!!!
I'm also new with crystal reports but i have already done a cross tab report using 8.5...anyway, u can use the wizard to guide u in creating ur crosstab...or u can explain further what u want to do so we could help each other in solving ur problem... :)|||I created a cross tab query in Ms Access..hoping to link to Crystal Report 7. But when i tried to arrange the columns n rows..i cant edit anything.eg: There's summarized field to be set in Crystal report 7. I dont want it to count my field..instead displaying just the data. How can i do it?

Cross-tab Row Title

CR XI posted a work-around/solution for inserting a row title in a cross tab report by creating a separate "fake" group, overlaying it and copying the cross tab to the fake group's section. It also discussed horizontal pages.

How do you repeat the label on subsequent/multiple pages though?See if you find answer here

http://support.businessobjects.com/

Crosstab Queries in SQL Server 2005

Hi there,

I'm trying to make a cross tab query in SQL Server 2005 SP2, I have it done in MS Access but I want to switch all my queries that use crosstabs into SQL.

I find this Query on the web:

Code Snippet

CREATE PROCEDURE [sp_CrossTabIntoTable]

@.select varchar(8000),

@.sumfunc varchar(100),

@.pivot varchar(100),

@.table varchar(100)

AS

DECLARE @.sql varchar(8000), @.delim varchar(1)

SET NOCOUNT ON

SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @.pivot + ' AS pivot INTO ##pivot FROM ' + @.table + ' WHERE 1=2')

EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' + @.table + ' WHERE '

+ @.pivot + ' Is Not Null')

SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' END)' )

SELECT @.delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )

WHEN 0 THEN '' ELSE '''' END

FROM tempdb.information_schema.columns

WHERE table_name='##pivot' AND column_name='pivot'

SELECT @.sql=@.sql + '''' + convert(varchar(100), pivot) + ''' = ' +

stuff(@.sumfunc,charindex( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN '

+ @.delim + convert(varchar(100), pivot) + @.delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @.sql=left(@.sql, len(@.sql)-1)

SELECT @.select=stuff(@.select, charindex(' FROM ', @.select)+1, 0, ', ' + @.sql + ' ')

EXEC (@.select)

SET ANSI_WARNINGS ON

But when I tried to run it I got this error message:

Msg 156, Level 15, State 1, Procedure sp_CrossTabIntoTable, Line 25

Incorrect syntax near the keyword 'pivot'.

I need it ASAP because I need to create new reports and I don't want to use MS Access no more.

Is there someone who knows what the problem is?

Thanks in advance,

Julien

PIVOT is a reserved keyword in 2005. Either change the column name eg. Pvt or put identifiers around the column name eg 'Pivot'

HTH
|||

Ok PIVOT is the operator/keyword in SQL Server 2005 use the following query...

Code Snippet

CREATE PROCEDURE [sp_CrossTabIntoTable]

@.select varchar(8000),

@.sumfunc varchar(100),

@.pivot varchar(100),

@.table varchar(100)

AS

DECLARE @.sql varchar(8000), @.delim varchar(1)

SET NOCOUNT ON

SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @.pivot + ' AS [pivot] INTO ##pivot FROM ' + @.table + ' WHERE 1=2')

EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' + @.table + ' WHERE '

+ @.pivot + ' Is Not Null')

SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' END)' )

SELECT @.delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )

WHEN 0 THEN '' ELSE '''' END

FROM tempdb.information_schema.columns

WHERE table_name='##pivot' AND column_name='pivot'

SELECT @.sql=@.sql + '''' + convert(varchar(100), pivot) + ''' = ' +

stuff(@.sumfunc,charindex( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN '

+ @.delim + convert(varchar(100), pivot) + @.delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @.sql=left(@.sql, len(@.sql)-1)

SELECT @.select=stuff(@.select, charindex(' FROM ', @.select)+1, 0, ', ' + @.sql + ' ')

EXEC (@.select)

SET ANSI_WARNINGS ON

|||THANK YOU BOTH!!

I finally get it working!!

Best regards

Julien

Crosstab Help in Sql Server

I need some assistance, i have this Stored Procedure that will take my table
and create a cross tab. Unfor, I am getting duplicate value from the output,
when checking table1, there are no dups, i dont' know what i am doing wrong.
Please help
below, code that I am using. thaks
=================================
DECLARE @.Month_1_V as varchar(20)
DECLARE @.Create_Date_V as Datetime
DECLARE @.Corp_V as Numeric(13)
DECLARE @.Source_V as Varchar(20)
DECLARE @.Category_V as Varchar(50)
DECLARE @.Description_1_V as Varchar(50)
DECLARE @.Cycle_V as Varchar(1)
DECLARE @.Count_1_V as Numeric(13)
DECLARE @.Month_1_V2 as varchar(20)
DECLARE @.Create_Date_V2 as Datetime
DECLARE @.Corp_V2 as Numeric(13)
DECLARE @.Source_V2 as Varchar(20)
DECLARE @.Category_V2 as Varchar(50)
DECLARE @.Description_1_V2 as Varchar(50)
DECLARE @.Cycle_V2 as Varchar(1)
DECLARE @.Count_1_V2 as Numeric(13)
DECLARE @.V1 as Numeric(13)
DECLARE @.V2 as Numeric(13)
DECLARE @.V3 as Numeric(13)
DECLARE @.V4 as Numeric(13)
DECLARE @.V5 as Numeric(13)
DECLARE @.V6 as Numeric(13)
DECLARE @.V7 as Numeric(13)
DECLARE @.V8 as Numeric(13)
DECLARE @.V9 as Numeric(13)
DECLARE @.V10 as Numeric(13)
DECLARE @.V11 as Numeric(13)
truncate table tbl_Agency
DECLARE t_Noble_Agency CURSOR
FOR
SELECT DISTINCT Month_1, Create_date, Corp, Source, Category, SUM(Count_1)
AS Count_1, Description_1, Cycle
FROM phonecol.temp_tblAgency
WHERE (Create_date BETWEEN CONVERT(DATETIME, '2006-05-12 00:00:00', 102)
AND CONVERT(DATETIME, '2006-05-12 00:00:00', 102))
GROUP BY Month_1, Create_date, Corp, Source, Category, Description_1, Cycle
ORDER BY Category, Corp, Source
OPEN t_Noble_Agency
FETCH NEXT FROM t_Noble_Agency INTO @.Month_1_V, @.Create_Date_V, @.Corp_V,
@.Source_V, @.Category_V, @.Count_1_V, @.Description_1_V, @.Cycle_V
IF @.@.FETCH_STATUS = 0
BEGIN
Set @.Month_1_V2 = @.Month_1_V
SET @.Create_Date_V2 = @.Create_Date_V
SET @.Corp_V2 = @.Corp_V
SET @.Source_V2 = @.Source_V
SET @.Category_V2 = @.Category_V
SET @.Cycle_V2 = @.Cycle_V
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
IF (@.Category_V2 <> @.Category_V)or(@.Corp_V2 <> @.Corp_V) or (@.Source_V2 <>
@.Source_V)
BEGIN
INSERT INTO tbl_Agency(Month_1, Type, Loaddate, Agency, Corp, Cycle,
Total_Accounts,Call_Backs, [Left_Msg(Machine)], [Left_Msg(Live)],
Promise_to_Pay, Full_Pay, Partial_Pay, Past_Due_Pay,
Wrong_Number,TriTones,Skip_Trace,Not_Rep
orted)
VALUES (@.Month_1_V2 , @.Category_V2, @.Create_Date_V2, @.Source_V2, @.Corp_V2,
@.Cycle_V2,
isnull(@.v1,0)+isnull(@.v2,0)+isnull(@.v3,0
)+isnull(@.v4,0)+isnull(@.v5,0)+isnull
(@.v6,0)+isnull(@.v7,0)+isnull(@.v8,0)+
isnull(@.v9,0)+isnull(@.v10,0)+isnull(@.v11
,0), isnull(@.v1,0), isnull(@.v2,0),
isnull(@.v3,0), isnull(@.v4,0), isnull(@.v5,0), isnull(@.v6,0), isnull(@.v7,0),
isnull(@.v8,0), isnull(@.v9,0),isnull(@.v10,0),isnull(@.v11
,0))
Set @.Month_1_V2 = @.Month_1_V
SET @.Create_Date_V2 = @.Create_Date_V
SET @.Corp_V2 = @.Corp_V
SET @.Source_V2 = @.Source_V
SET @.Category_V2 = @.Category_V
SET @.Cycle_V2 = @.Cycle_V
END
--@.V1 = 'Call_Backs'
--@.V2 = '[Left_Msg(Machine)]'
--@.V3 = '[Left_Msg(Live)]'
--@.V4 = 'Promise_to_Pay'
--@.V5 = 'Full_Pay'
--@.V6 = 'Partial_Pay'
--@.V7 = 'Past_Due_Pay'
--@.V8 = 'Wrong_Number'
--@.V9= 'TriTones'
--@.V10 = 'Skip_Trace'
--@.V11 = 'Not_Reported'
IF ltrim(rtrim(@.Description_1_V)) = 'Call Back'
Begin
SET @.V1 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Left Message (Answering Machine)'
Begin
SET @.V2 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Left Message (Live Person)'
Begin
SET @.V3 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Promise To Pay'
Begin
SET @.V4 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Full)'
Begin
SET @.V5 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Partial Pmt)'
Begin
SET @.V6 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Past Due)'
Begin
SET @.V7 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Wrong Number'
Begin
SET @.V8 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Tri-Tones'
Begin
SET @.V9 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Skip Trace Customers Removed Prior to
Contact'
Begin
SET @.V10 = @.Count_1_V
END
IF ltrim(rtrim(@.Description_1_V)) = 'Not Reported'
Begin
SET @.V11 = @.Count_1_V
END
END
FETCH NEXT FROM t_Noble_Agency INTO @.Month_1_V, @.Create_Date_V, @.Corp_V,
@.Source_V, @.Category_V, @.Count_1_V, @.Description_1_V, @.Cycle_V
END
INSERT INTO tbl_Agency(Month_1, Type, Loaddate, Agency, Corp, Cycle,
Total_Accounts,Call_Backs, [Left_Msg(Machine)], [Left_Msg(Live)],
Promise_to_Pay, Full_Pay, Partial_Pay, Past_Due_Pay,
Wrong_Number,TriTones,Skip_Trace,Not_Rep
orted)
VALUES (@.Month_1_V2 , @.Category_V2, @.Create_Date_V2, @.Source_V2, @.Corp_V2,
@.Cycle_V2,
isnull(@.v1,0)+isnull(@.v2,0)+isnull(@.v3,0
)+isnull(@.v4,0)+isnull(@.v5,0)+isnull
(@.v6,0)+isnull(@.v7,0)+isnull(@.v8,0)+
isnull(@.v9,0)+isnull(@.v10,0)+isnull(@.v11
,0), isnull(@.v1,0), isnull(@.v2,0),
isnull(@.v3,0), isnull(@.v4,0), isnull(@.v5,0), isnull(@.v6,0), isnull(@.v7,0),
isnull(@.v8,0), isnull(@.v9,0),isnull(@.v10,0),isnull(@.v11
,0))
END
CLOSE t_Noble_Agency
DEALLOCATE t_Noble_Agency
GOHi Justin,
I didn't look close into the SP. But from a high level I find that you
have two inserts for 1 fetch within the while loop (one inside the if
condition and one outside in the end). Can you tell why you are doing it.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Justin" wrote:

> I need some assistance, i have this Stored Procedure that will take my tab
le
> and create a cross tab. Unfor, I am getting duplicate value from the outpu
t,
> when checking table1, there are no dups, i dont' know what i am doing wron
g.
> Please help
> below, code that I am using. thaks
> =================================
> DECLARE @.Month_1_V as varchar(20)
> DECLARE @.Create_Date_V as Datetime
> DECLARE @.Corp_V as Numeric(13)
> DECLARE @.Source_V as Varchar(20)
> DECLARE @.Category_V as Varchar(50)
> DECLARE @.Description_1_V as Varchar(50)
> DECLARE @.Cycle_V as Varchar(1)
> DECLARE @.Count_1_V as Numeric(13)
> DECLARE @.Month_1_V2 as varchar(20)
> DECLARE @.Create_Date_V2 as Datetime
> DECLARE @.Corp_V2 as Numeric(13)
> DECLARE @.Source_V2 as Varchar(20)
> DECLARE @.Category_V2 as Varchar(50)
> DECLARE @.Description_1_V2 as Varchar(50)
> DECLARE @.Cycle_V2 as Varchar(1)
> DECLARE @.Count_1_V2 as Numeric(13)
> DECLARE @.V1 as Numeric(13)
> DECLARE @.V2 as Numeric(13)
> DECLARE @.V3 as Numeric(13)
> DECLARE @.V4 as Numeric(13)
> DECLARE @.V5 as Numeric(13)
> DECLARE @.V6 as Numeric(13)
> DECLARE @.V7 as Numeric(13)
> DECLARE @.V8 as Numeric(13)
> DECLARE @.V9 as Numeric(13)
> DECLARE @.V10 as Numeric(13)
> DECLARE @.V11 as Numeric(13)
> truncate table tbl_Agency
> DECLARE t_Noble_Agency CURSOR
> FOR
> SELECT DISTINCT Month_1, Create_date, Corp, Source, Category, SUM(Count_1)
> AS Count_1, Description_1, Cycle
> FROM phonecol.temp_tblAgency
> WHERE (Create_date BETWEEN CONVERT(DATETIME, '2006-05-12 00:00:00', 10
2)
> AND CONVERT(DATETIME, '2006-05-12 00:00:00', 102))
> GROUP BY Month_1, Create_date, Corp, Source, Category, Description_1, Cycl
e
> ORDER BY Category, Corp, Source
> OPEN t_Noble_Agency
> FETCH NEXT FROM t_Noble_Agency INTO @.Month_1_V, @.Create_Date_V, @.Corp_V,
> @.Source_V, @.Category_V, @.Count_1_V, @.Description_1_V, @.Cycle_V
> IF @.@.FETCH_STATUS = 0
> BEGIN
> Set @.Month_1_V2 = @.Month_1_V
> SET @.Create_Date_V2 = @.Create_Date_V
> SET @.Corp_V2 = @.Corp_V
> SET @.Source_V2 = @.Source_V
> SET @.Category_V2 = @.Category_V
> SET @.Cycle_V2 = @.Cycle_V
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF (@.@.FETCH_STATUS <> -2)
> BEGIN
> IF (@.Category_V2 <> @.Category_V)or(@.Corp_V2 <> @.Corp_V) or (@.Source_V2 <>
> @.Source_V)
> BEGIN
> INSERT INTO tbl_Agency(Month_1, Type, Loaddate, Agency, Corp, Cycle,
> Total_Accounts,Call_Backs, [Left_Msg(Machine)], [Left_Msg(Live)],
> Promise_to_Pay, Full_Pay, Partial_Pay, Past_Due_Pay,
> Wrong_Number,TriTones,Skip_Trace,Not_Rep
orted)
> VALUES (@.Month_1_V2 , @.Category_V2, @.Create_Date_V2, @.Source_V2, @.Corp_V2,
> @.Cycle_V2,
> isnull(@.v1,0)+isnull(@.v2,0)+isnull(@.v3,0
)+isnull(@.v4,0)+isnull(@.v5,0)+isnu
ll(@.v6,0)+isnull(@.v7,0)+isnull(@.v8,0)+
> isnull(@.v9,0)+isnull(@.v10,0)+isnull(@.v11
,0), isnull(@.v1,0), isnull(@.v2,0),
> isnull(@.v3,0), isnull(@.v4,0), isnull(@.v5,0), isnull(@.v6,0), isnull(@.v7,0),
> isnull(@.v8,0), isnull(@.v9,0),isnull(@.v10,0),isnull(@.v11
,0))
> Set @.Month_1_V2 = @.Month_1_V
> SET @.Create_Date_V2 = @.Create_Date_V
> SET @.Corp_V2 = @.Corp_V
> SET @.Source_V2 = @.Source_V
> SET @.Category_V2 = @.Category_V
> SET @.Cycle_V2 = @.Cycle_V
> END
> --@.V1 = 'Call_Backs'
> --@.V2 = '[Left_Msg(Machine)]'
> --@.V3 = '[Left_Msg(Live)]'
> --@.V4 = 'Promise_to_Pay'
> --@.V5 = 'Full_Pay'
> --@.V6 = 'Partial_Pay'
> --@.V7 = 'Past_Due_Pay'
> --@.V8 = 'Wrong_Number'
> --@.V9= 'TriTones'
> --@.V10 = 'Skip_Trace'
> --@.V11 = 'Not_Reported'
> IF ltrim(rtrim(@.Description_1_V)) = 'Call Back'
> Begin
> SET @.V1 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Left Message (Answering Machine)'
> Begin
> SET @.V2 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Left Message (Live Person)'
> Begin
> SET @.V3 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Promise To Pay'
> Begin
> SET @.V4 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Full)'
> Begin
> SET @.V5 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Partial Pmt)'
> Begin
> SET @.V6 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Received Payment (Past Due)'
> Begin
> SET @.V7 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Wrong Number'
> Begin
> SET @.V8 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Tri-Tones'
> Begin
> SET @.V9 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Skip Trace Customers Removed Prior to
> Contact'
> Begin
> SET @.V10 = @.Count_1_V
> END
> IF ltrim(rtrim(@.Description_1_V)) = 'Not Reported'
> Begin
> SET @.V11 = @.Count_1_V
> END
> END
> FETCH NEXT FROM t_Noble_Agency INTO @.Month_1_V, @.Create_Date_V, @.Corp_V,
> @.Source_V, @.Category_V, @.Count_1_V, @.Description_1_V, @.Cycle_V
> END
> INSERT INTO tbl_Agency(Month_1, Type, Loaddate, Agency, Corp, Cycle,
> Total_Accounts,Call_Backs, [Left_Msg(Machine)], [Left_Msg(Live)],
> Promise_to_Pay, Full_Pay, Partial_Pay, Past_Due_Pay,
> Wrong_Number,TriTones,Skip_Trace,Not_Rep
orted)
> VALUES (@.Month_1_V2 , @.Category_V2, @.Create_Date_V2, @.Source_V2, @.Corp_V2,
> @.Cycle_V2,
> isnull(@.v1,0)+isnull(@.v2,0)+isnull(@.v3,0
)+isnull(@.v4,0)+isnull(@.v5,0)+isnu
ll(@.v6,0)+isnull(@.v7,0)+isnull(@.v8,0)+
> isnull(@.v9,0)+isnull(@.v10,0)+isnull(@.v11
,0), isnull(@.v1,0), isnull(@.v2,0),
> isnull(@.v3,0), isnull(@.v4,0), isnull(@.v5,0), isnull(@.v6,0), isnull(@.v7,0),
> isnull(@.v8,0), isnull(@.v9,0),isnull(@.v10,0),isnull(@.v11
,0))
> END
> CLOSE t_Noble_Agency
> DEALLOCATE t_Noble_Agency
> GO|||sure,
what happens first is that data will be imported to a temporary table
from there, the SP should take the data from that table and change it to a
crosstab
inserting output to finalize table
unfor, in the finalize table, i am getting duplicated values
"Omnibuzz" wrote:
> Hi Justin,
> I didn't look close into the SP. But from a high level I find that you
> have two inserts for 1 fetch within the while loop (one inside the if
> condition and one outside in the end). Can you tell why you are doing it.
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Justin" wrote:
>|||Where is the insert script for the finalise table?
I assume that tbl_Agency is the temp table you are referring to and it can
have duplicates. Can you post the cross tab query?
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Justin" wrote:
> sure,
> what happens first is that data will be imported to a temporary table
> from there, the SP should take the data from that table and change it to a
> crosstab
> inserting output to finalize table
> unfor, in the finalize table, i am getting duplicated values
>
> "Omnibuzz" wrote:
>

CrossTab

Hi Guys!
Is there anything like cross tab of access in sql server?
Thanks.Hafez Rabbani (hafezrabbani@.gmail.com) writes:
> Hi Guys!
> Is there anything like cross tab of access in sql server?

You can do cross-tabs in SQL Server, but it it is not as straightforward
as it is in Access.

There is a section in Books Online that gives some tips about this:
Accessing and Changing Relational Data / Transact-SQL Tips / Cross-Tab
Reports

That section covers static crosstabs. Dynamic crosstabs requires use of
dynamic SQL and requires more work. There is a third-party product, RAC,
which is popular for this, check out http://www.rac4sql.net/.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Cross tabs with Drill downs

Hi Guys
I need to generate a report where I need to have drill downs in a matrix(cross tab query) in Business Intelligence.
When I try to build a report using the report wizard, I can either select matrix or Drill downs and subtotals.
Can I have both of these options?

Thanks
Mita

Moving to AS forum.

Cross Tab?

I need to format the following tables:

tbEmployees
EmployeeID | fName | lName
--------------
jdoe | Joe | Doe
bsmith | Blake | Smith

tbDepartments
DepartmentID | Department
----------
ENG | Engineering
DET | Detailing

tbDepartmentEmployees
fkEmployee ID | fkEmployeeID
----------
ENG | jdoe
DET | bsmith

tbProjects
ProjectID |
-----
1001

tbProjectTeam
fkProjectID | fkEmployeeID | fkDepartmentID
--------------
1001 | jdoe | ENG
1001 | bsmith | DET

To the following view :

vProjects
ProjectID | Engineer | Detailer
------------
1001 | Joe Doe | Blake Smith

Any Ideas?
Mike BSQL server does not have built-in crosstab querys as Access does. You can replicate a cross-tab query using CASE statements. It looks intimidating at first, but it actually pretty straight-forward. Look up Crosstab in Books Online for a good explanation and example.

cross tab?

thought my question was misinterpreted.

actual table can be smaller or larger. but,
within a procedure need to turn a created #temptable
similar to(examid is in asc order):
(id, p/f, examid, examname)
1 f xxxx xxxxx
1 f xxxx xxxxx
1 f xxxx xxxxx
2 p
3 p
4 f xxxx xxxxx
4 f xxxx xxxxx
4 f xxxx xxxxx
4 f xxxx xxxxx
4 f xxxx xxxxx
4 f xxxx xxxxx
4 f xxxx xxxxx

(note, id = 4 has 7 failed exams).

to a table with up to five columns of failed
examnames:

1 f xxxxx xxxxx xxxxx
2 p
2 p
4 f xxxxx xxxxx xxxxx xxxxx xxxxx

can get rid of exameid if helps.

--
Sent by ricksql from yahoo subpart from com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com/cgi/content/newso what you need is a crosstab table with a 5 recors limit only ?

Jens Smeyer.|||Dear Jens S|_meyer,

> so what you need is a crosstab table with a 5 recors limit only ?
> Jens S|_meyer.

yes.

within a procedure need to turn a created #temptable
similar to:
(id, p/f, examid, examname)
1 f xxxx xxxxx
1 f xxxx xxxxx
1 f xxxx xxxxx
2 p
3 p
4 f xxxx xxxxx
4 f xxxx xxxxx
4 f xxxx xxxxx
4 f xxxx xxxxx
4 f xxxx xxxxx
4 f xxxx xxxxx
4 f xxxx xxxxx

(note, id = 4 has 7 failed exams).

to a table with up to five columns of failed
examnames:

1 f xxxxx xxxxx xxxxx
2 p
2 p
4 f xxxxx xxxxx xxxxx xxxxx xxxxx

can get rid of exameid if helps.

--
Spam protected message from:
Sent by ricksql from yahoo subpart from com
Posted via http://www.usenet-replayer.com/cgi/content/new

Cross Tab/Pivot table/UDF?

I have the following tables:

tbTemplateShapeProperties
fkTemplate | fkProperty | PropertyValue
---------------
1 | 1 | 192
1 | 2 | 36
1 | 3 | 4
1 | 4 | 5
1 | 5 | 2

tbShapeProperties
Property | PropertyName | fkShape
--------------
1 | Width | 1
2 | Height | 1
3 | Flange | 1
4 | Avg. Leg Width | 5
5 | Leg Count | 2

From the above I wanted to create a pivot table, from there I want to pass the column values through to a UDF

XSection (Width, Height, Flange, Leg, LegCount)

I tried the following to get a pivot table but it does not give a single row but 5.

SELECT CASE sp.PropertyName WHEN 'Width' THEN tsp.PropertyValue ELSE 0 END AS Width,
CASE sp.PropertyName WHEN 'Height' THEN tsp.PropertyValue ELSE 0 END AS Height,
CASE sp.PropertyName WHEN 'Flange' THEN tsp.PropertyValue ELSE 0 END AS Flange,
CASE sp.PropertyName WHEN 'Avg. Leg Width' THEN tsp.PropertyValue ELSE 0 END AS Leg,
CASE sp.PropertyName WHEN 'Leg Count' THEN tsp.PropertyValue ELSE 0 END AS LegCount
FROM tbTemplateShapeProperties AS tsp INNER JOIN tbShapeProperties AS sp
ON tsp.fkProperty = sp.Property
WHERE tsp.fkTemplate = 1

The following results are returned:

Width | Height | Flange | Leg | LegCount
--------------
192 | 0 | 0 | 0 | 0
0 | 36 | 0 | 0 | 0
0 | 0 | 6 | 0 | 0
0 | 0 | 0 | 5 | 0
0 | 0 | 0 | 0 | 2

The desired result as you could guess is:

Width | Height | Flange | Leg | LegCount
--------------
192 | 36 | 6 | 5 | 2

So, this leaves me with one question, even if I was to get this to work, is is possible to then extract the values and pass them through to the UDF within the same stored proc?

Any hints?

Mike BYeah, I did it!!!

Here is the code:

CREATE PROCEDURE usp_GetDoubleTCrossSection

@.iTemplate int,
@.fXSection float OUTPUT

AS

declare @.fWidth float, @.fHeight float, @.fFlange float, @.fLegWidth float, @.fLegs float

set @.iTemplate = 1
SELECT @.fWidth = SUM(CASE sp.PropertyName WHEN 'Width' THEN tsp.PropertyValue ElSE 0 END),
@.fHeight = SUM(CASE sp.PropertyName WHEN 'Height' THEN tsp.PropertyValue ElSE 0 END),
@.fFlange = SUM(CASE sp.PropertyName WHEN 'Flange' THEN tsp.PropertyValue ElSE 0 END),
@.fLegWidth = SUM(CASE sp.PropertyName WHEN 'Avg. Leg Width' THEN tsp.PropertyValue ElSE 0 END),
@.fLegs = SUM(CASE sp.PropertyName WHEN 'Leg Count' THEN tsp.PropertyValue ElSE 0 END)
FROM tbTemplateShapeProperties AS tsp INNER JOIN tbShapeProperties AS sp
ON tsp.fkProperty = sp.Property
WHERE tsp.fkTemplate = @.iTemplate

SELECT @.fXSection = [dbo].[TEE_XSECTION](@.fWidth, @.fHeight, @.fFlange, @.fLegWidth, @.fLegs)
GO

//UDF
CREATE FUNCTION TEE_XSECTION
(@.fWidth float, @.fHeight float, @.fFlange float, @.fLegWidth float, @.fLegs float)

RETURNS float

AS
BEGIN
RETURN (@.fWidth * @.fFlange + (((@.fHeight - @.fFlange)*@.fLegWidth)*@.fLegs))
END

No cursor!!! Whew!

Mike B

cross tab type query in sql

I have three tables:
tblBook has the fields bookID, bookRangeID, bookSubjectID, bookCode
tblBookRange has the fields bookRangeID, bookRangeDescription
tblBookSubject has the fields bookSubjectID, bookSubjectDescription
so some typical data in tblBook might be:
1, 1, 1, B1HBSCI
2, 1, 2, B2HBFRE1
3, 1, 3, B3HBGER
4, 2, 1, B4PBSCI
5, 2, 2, B5PBFRE
6, 2, 3, B6PBGER
7, 3, 1, B7CDSCI
8, 3, 2, B8CDFRE
9, 3, 3, B9CDGER1
10, 3, 3, B10CDGER2
11, 1, 2, B11HBFRE2
tblBookRange would be:
1, HardBack
2, PaperBack
3, CD Rom
tblBookSubject would be:
1, Science
2, French
3, German
I'd like to create a query which will return me the subjects along the
top, the book range down the side, and the bookcodes in the cells, a
bit like this:
BookRange , Science, French, German
HardBack , B1HBSCI, B2HBFRE1 B11HBFRE2, B3HBGER
PaperBack , B4PBSCI, B5PBFRE, B6PBGER
CD Rom , B7CDSCI, B8CDFRE, B9CDGER1 B10CDGER2
Does that make any sense? So basically I'd like to get some kind of
dynamic SQL working which will do this kind of thing. I don't want to
hard code the subjects in or the book ranges. I get the feeling that
dynamic SQL is the way forward with this and possibly using a cursor or
two too, but it got quite nasty and convoluted when I tried various
attempts to get it working. (one of the ways I tried included working
out each result in a dynamic script, but it ran out of characters as
there were too many "subjects".)
If anyone has any nice but quite dynamic solutions, I'd be delighted to
hear.
(and I know some of you have already told me you don't like tables
beginnig with tbl, but I'm not hear for a lecture on naming
conventions, I'm hear to learn and share ideas :o) )The RAC utility could do this for you quite easily (similiar in
concept to Access crosstab but much more powerful).
But then you wouldn't have the opportunity to write your
own dynamic code and maintain it:)
www.rac4sql.net|||The RAC utility is well worth the relatively inexpensive cost. I think it i
s
$60 per database or $300+ for a site license. If you are doing many crossta
b
queries, you will save this much in time in the first couple of days.
Another thing to note though, the not-yet-officially-released SQL Server 200
5
has a pivot function which will easliy perform crosstab queries.
Archer
"Pike" wrote:

> The RAC utility could do this for you quite easily (similiar in
> concept to Access crosstab but much more powerful).
> But then you wouldn't have the opportunity to write your
> own dynamic code and maintain it:)
> www.rac4sql.net
>
>|||If you are using SQL 2000/SQL 7.0, then yes you have to use dynamic SQL (or
a
report writer that uses dynamic SQL) and all the evil that goes with it or h
ave
your report writer create your crosstab type result. You could also use
something like Excel (or other report writer) to call to a stored proc to ge
t
the data you want and have Excel do the pivoting/crosstabbing.
Thomas
"Neil" <neildog_remove*@._remove_majiccarpet.fsnet.co.uk> wrote in message
news:d753v9$11b$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
>I have three tables:
>
> tblBook has the fields bookID, bookRangeID, bookSubjectID, bookCode
>
> tblBookRange has the fields bookRangeID, bookRangeDescription
>
> tblBookSubject has the fields bookSubjectID, bookSubjectDescription
>
> so some typical data in tblBook might be:
>
> 1, 1, 1, B1HBSCI
> 2, 1, 2, B2HBFRE1
> 3, 1, 3, B3HBGER
> 4, 2, 1, B4PBSCI
> 5, 2, 2, B5PBFRE
> 6, 2, 3, B6PBGER
> 7, 3, 1, B7CDSCI
> 8, 3, 2, B8CDFRE
> 9, 3, 3, B9CDGER1
> 10, 3, 3, B10CDGER2
> 11, 1, 2, B11HBFRE2
> tblBookRange would be:
>
> 1, HardBack
> 2, PaperBack
> 3, CD Rom
>
> tblBookSubject would be:
>
> 1, Science
> 2, French
> 3, German
>
> I'd like to create a query which will return me the subjects along the
> top, the book range down the side, and the bookcodes in the cells, a
> bit like this:
>
> BookRange , Science, French, German
> HardBack , B1HBSCI, B2HBFRE1 B11HBFRE2, B3HBGER
> PaperBack , B4PBSCI, B5PBFRE, B6PBGER
> CD Rom , B7CDSCI, B8CDFRE, B9CDGER1 B10CDGER2
>
> Does that make any sense? So basically I'd like to get some kind of
> dynamic SQL working which will do this kind of thing. I don't want to
> hard code the subjects in or the book ranges. I get the feeling that
> dynamic SQL is the way forward with this and possibly using a cursor or
> two too, but it got quite nasty and convoluted when I tried various
> attempts to get it working. (one of the ways I tried included working
> out each result in a dynamic script, but it ran out of characters as
> there were too many "subjects".)
>
> If anyone has any nice but quite dynamic solutions, I'd be delighted to
> hear.
>
> (and I know some of you have already told me you don't like tables
> beginnig with tbl, but I'm not hear for a lecture on naming
> conventions, I'm hear to learn and share ideas :o) )
>
>|||Does RAC simply do the dynamic SQL for you (i.e. build a dynamic SQL stateme
nt
and return the results)?
Thomas
"bagman3rd" <bagman3rd@.discussions.microsoft.com> wrote in message
news:10B2ABB7-0B0B-4AC3-9CDA-BC7721E62EAC@.microsoft.com...
> The RAC utility is well worth the relatively inexpensive cost. I think it
is
> $60 per database or $300+ for a site license. If you are doing many cross
tab
> queries, you will save this much in time in the first couple of days.
> Another thing to note though, the not-yet-officially-released SQL Server 2
005
> has a pivot function which will easliy perform crosstab queries.
> Archer
> "Pike" wrote:
>|||You could do something like this:
create table tblBook (
bookID int, bookRangeID int, bookSubjectID int, bookCode varchar(50))
insert into tblBook values(1, 1, 1, 'B1HBSCI')
insert into tblBook values(2, 1, 2, 'B2HBFRE1')
insert into tblBook values(3, 1, 3, 'B3HBGER')
insert into tblBook values(4, 2, 1, 'B4PBSCI')
insert into tblBook values(5, 2, 2, 'B5PBFRE')
insert into tblBook values(6, 2, 3, 'B6PBGER')
insert into tblBook values(7, 3, 1, 'B7CDSCI')
insert into tblBook values(8, 3, 2, 'B8CDFRE')
insert into tblBook values(9, 3, 3, 'B9CDGER1')
insert into tblBook values(10, 3, 3, 'B10CDGER2')
insert into tblBook values(11, 1, 2, 'B11HBFRE2')
create table tblBookRange (bookRangeID int, bookRangeDescription
varchar(50))
insert into tblBookRange values (1, 'HardBack')
insert into tblBookRange values (2, 'PaperBack')
insert into tblBookRange values (3, 'CD Rom')
create table tblBookSubject(bookSubjectID int, bookSubjectDescription
varchar(50))
insert into tblBookSubject values(1, 'Science')
insert into tblBookSubject values(2, 'French')
insert into tblBookSubject values(3, 'German')
BookRange , Science, French, German
HardBack , B1HBSCI, B2HBFRE1 B11HBFRE2, B3HBGER
PaperBack , B4PBSCI, B5PBFRE, B6PBGER
CD Rom , B7CDSCI, B8CDFRE, B9CDGER1 B10CDGER2
declare variables
declare @.p char(1000)
declare @.i char(12)
declare @.cntm int
declare @.cntn int
declare @.m int
declare @.n int
set @.p = ''
select @.cntm=count(distinct bookRangeID) from tblBookRange
set @.m = 1
select @.cntn=count(distinct bookSubjectID) from tblBookSubject
set @.n = 1
Process until no more items
while @.m <= @.cntm
begin
while @.n <= @.cntn
begin
string together all items with a comma between
select @.i = bookRangeDescription, @.p = rtrim(@.p) + ' ' + bookCode
from tblBook a join tblbookRange b on a.bookRangeID = b.bookRangeID
where a.bookRangeID = @.m and a.bookSubjectID = @.n
set @.n = @.n + 1
set @.p = rtrim(@.p) + ','
end
print detail row
print @.i + ' ' + rtrim(substring(@.p,1,len(@.p)))
set @.n = 1
set @.m = @.m + 1
set @.p = ''
end
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Neil" <neildog_remove*@._remove_majiccarpet.fsnet.co.uk> wrote in message
news:d753v9$11b$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
> I have three tables:
>
> tblBook has the fields bookID, bookRangeID, bookSubjectID, bookCode
>
> tblBookRange has the fields bookRangeID, bookRangeDescription
>
> tblBookSubject has the fields bookSubjectID, bookSubjectDescription
>
> so some typical data in tblBook might be:
>
> 1, 1, 1, B1HBSCI
> 2, 1, 2, B2HBFRE1
> 3, 1, 3, B3HBGER
> 4, 2, 1, B4PBSCI
> 5, 2, 2, B5PBFRE
> 6, 2, 3, B6PBGER
> 7, 3, 1, B7CDSCI
> 8, 3, 2, B8CDFRE
> 9, 3, 3, B9CDGER1
> 10, 3, 3, B10CDGER2
> 11, 1, 2, B11HBFRE2
> tblBookRange would be:
>
> 1, HardBack
> 2, PaperBack
> 3, CD Rom
>
> tblBookSubject would be:
>
> 1, Science
> 2, French
> 3, German
>
> I'd like to create a query which will return me the subjects along the
> top, the book range down the side, and the bookcodes in the cells, a
> bit like this:
>
> BookRange , Science, French, German
> HardBack , B1HBSCI, B2HBFRE1 B11HBFRE2, B3HBGER
> PaperBack , B4PBSCI, B5PBFRE, B6PBGER
> CD Rom , B7CDSCI, B8CDFRE, B9CDGER1 B10CDGER2
>
> Does that make any sense? So basically I'd like to get some kind of
> dynamic SQL working which will do this kind of thing. I don't want to
> hard code the subjects in or the book ranges. I get the feeling that
> dynamic SQL is the way forward with this and possibly using a cursor or
> two too, but it got quite nasty and convoluted when I tried various
> attempts to get it working. (one of the ways I tried included working
> out each result in a dynamic script, but it ran out of characters as
> there were too many "subjects".)
>
> If anyone has any nice but quite dynamic solutions, I'd be delighted to
> hear.
>
> (and I know some of you have already told me you don't like tables
> beginnig with tbl, but I'm not hear for a lecture on naming
> conventions, I'm hear to learn and share ideas :o) )
>
>|||Yes. Here is the sql to execute a RAC stored procedure. The tool that
comes with RAC is not very straightforward, but manipulating the SQL to get
what you want is.
Execute rac
@.transform = 'max(ltrim(rtrim([qyRac_hits].[x]))) as result))',
@.rows = '[qyRac_hits].[test_name] & [qyRac_hits].[Analyte]',
@.pvtcol = '[qyRac_hits].[ENVIRON_sample_id_mod]',
@.racheck = 'y',
@.from = '[qyRac_hits]',
@.where = 'ENVIRON_sample_id_mod like ~%emw1-%~
and collection_date < ~4/1/2005~',
@.grand_totals='n',
@.defaultexceptions='Funct & Totals'
Archer
"Thomas Coleman" wrote:

> Does RAC simply do the dynamic SQL for you (i.e. build a dynamic SQL state
ment
> and return the results)?
>
> Thomas
>
> "bagman3rd" <bagman3rd@.discussions.microsoft.com> wrote in message
> news:10B2ABB7-0B0B-4AC3-9CDA-BC7721E62EAC@.microsoft.com...
>
>|||Hello Thomas,
For what it's worth:
Most novice and even experienced sql programmers assume RAC
builds a classic dynamic SELECT statement consisting of gobs
of classic CASE statements as seen in the plethora of sql books
illustrating an sql crosstab.This is NOT what RAC does.Given all
the functionality/features available, this method would be next to
impossible
and even if it could be done it probably would bring the server to a hault.
The basic dynamic query built is a SELECT/GROUP BY query based
on the transform (aggregate(s)), row(s) and the column to be pivoted.
This is the building block from which xtabs,various ranking functionality
and all other basic questions/options start with.The default method that
RAC uses to build the actual result utilitized bcp.So anything you see
in a text book or on a site that shows how to develope a dynamic
crosstab in t-sql bears absolutely no resemblence to RAC:)
The real goal of RAC is to unburdon the user/developer of the messy
details of creating a myraid of dynamic results for various data
manipulation problems without stressing out the query optimizer.
Another example of *What* not *How*:)
Best from,
www.rac4sql.net
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23kC1sSiYFHA.228@.TK2MSFTNGP12.phx.gbl...
> Does RAC simply do the dynamic SQL for you (i.e. build a dynamic SQL
> statement and return the results)?|||Understand, but quality of the code was not the reason I asked. My question
related to the permission issues with dynamic SQL. From what I understand, R
AC
would require providing direct table access to the users.
Thomas
"Pike" <stevenospam@.rac4sql.net> wrote in message
news:uCv1hqkYFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hello Thomas,
> For what it's worth:
> Most novice and even experienced sql programmers assume RAC
> builds a classic dynamic SELECT statement consisting of gobs
> of classic CASE statements as seen in the plethora of sql books
> illustrating an sql crosstab.This is NOT what RAC does.Given all
> the functionality/features available, this method would be next to impossi
ble
> and even if it could be done it probably would bring the server to a hault
.
> The basic dynamic query built is a SELECT/GROUP BY query based
> on the transform (aggregate(s)), row(s) and the column to be pivoted.
> This is the building block from which xtabs,various ranking functionality
> and all other basic questions/options start with.The default method that
> RAC uses to build the actual result utilitized bcp.So anything you see
> in a text book or on a site that shows how to develope a dynamic
> crosstab in t-sql bears absolutely no resemblence to RAC:)
> The real goal of RAC is to unburdon the user/developer of the messy
> details of creating a myraid of dynamic results for various data
> manipulation problems without stressing out the query optimizer.
> Another example of *What* not *How*:)
> Best from,
> www.rac4sql.net
> "Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
> news:%23kC1sSiYFHA.228@.TK2MSFTNGP12.phx.gbl...
>|||"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23g5cFSiYFHA.228@.TK2MSFTNGP12.phx.gbl...
> If you are using SQL 2000/SQL 7.0, then yes you have to use dynamic SQL
> (or a report writer that uses dynamic SQL) and all the evil that goes with
> it....
*Evil*...one would think that a professional might pick a more
appropriate term. But since its drummed into the head of every
user so often, it must be so:) Beware of piped pipers with long noses:)

Cross tab stored procedure

Hi
I have a table with rows of timestamped values for given variables. Each of the variables are periodically inserted at the same time. ie
DateTime Variable Value.
T1 V1 xxxx
T1 V2 xxxx
T1 V3 xxxx
T2 V1 yyyy
T2 V2 yyyy
T2 V3 yyyy
In 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 V3
T1 xxxx xxxx xxxx
T2 yyyy yyyy yyyy
any suggestions for an SQL statement.
thanks in advance.
I don't exactly understand your business requirements. Are V1, V2, and V3 the only possible values for the Variable column? If so, something like this might help to get you started:

SELECT
DateTime,
MIN(V1),
MIN(V2),
MIN(V3)
FROM
(
SELECT
DateTime,
CASE
WHEN Variable = 'V1' THEN Value ELSE NULL
END AS V1,
CASE
WHEN Variable = 'V2' THEN Value ELSE NULL
END AS V2,
CASE
WHEN Variable = 'V3' THEN Value ELSE NULL
END AS V3
FROM
yourTable
) AS subQuery
GROUP BY
DateTime


|||

Thanks Terri

The base table contains many variables. The data in this table is produced by a periodic time base event which inserts a row for each of the variables along with their current value. What I am trying to achieve is a record set where I can compare the values of selected variables in a single row based on the timestamp. ie
DateTime Var1 Var2 Var3
1/1/2005 10:00 Product1 10 20
1/1/2005 10:05 Product2 9 15
1/1/2005 10:10 Product2 12 30
1/1/2005 10:20 Product1 15 16
Once I have this record set I can then do selections based on the value of any variable ie
"select * from recorset where Var1 = 'Product1'"
I tried a statement similar to the one you have posted but my case statement sintax wasn't quite right.
I'm not in the office for a few days so may not get a chance try your code.
regards
Noel

Cross Tab SELECT

I am using MS-SQL2000, on a table called tblItems with these columns:
ID Descripction
-- --
0012 Washer
0145 Oven
2345 Heater
7834 Fan
and other called tblFeatures
ID Description
-- --
0012 White
0012 Large
0012 Electrical
0145 Brown
0145 Small
0145 Hybrid
I want to make a query with next result
ID Item F1 F2
F3
-- -- -- --
--
0012 Washer White Large
Electrical
0145 Oven Brown Small Hybri
d
.
.
.
How can I do this? Please, help me.
Beforehand, thank you very much.
Luis Garcia
IT ConsultantLuis, try:
SELECT ID, Item
, MAX(CASE WHEN pos = 1 THEN Feature END) AS F1
, MAX(CASE WHEN pos = 2 THEN Feature END) AS F2
, MAX(CASE WHEN pos = 3 THEN Feature END) AS F3
/* add more items here */
FROM (SELECT F.ID, I.Description AS Item, F.Description AS Feature,
(SELECT COUNT(*)
FROM tblFeatures AS F2
WHERE F2.ID = F.ID
AND F2.Description <= F.Description) AS Pos
FROM tblItems AS I
JOIN tblFeatures AS F
ON F.ID = I.ID) AS D
GROUP BY ID, Item;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"LUIS" <LUIS@.discussions.microsoft.com> wrote in message
news:6D153B9E-5373-49B3-8E27-AD6B69F27B35@.microsoft.com...
>I am using MS-SQL2000, on a table called tblItems with these columns:
> ID Descripction
> -- --
> 0012 Washer
> 0145 Oven
> 2345 Heater
> 7834 Fan
> and other called tblFeatures
> ID Description
> -- --
> 0012 White
> 0012 Large
> 0012 Electrical
> 0145 Brown
> 0145 Small
> 0145 Hybrid
> I want to make a query with next result
> ID Item F1 F2
> F3
> -- -- -- --
> --
> 0012 Washer White Large
> Electrical
> 0145 Oven Brown Small
> Hybrid
> .
> .
> .
> How can I do this? Please, help me.
> Beforehand, thank you very much.
>
> --
> Luis Garcia
> IT Consultant|||Itzik Ben-Gan
Thank you very much. Your answer was very useful to me.
Luis Garcia
IT Consultant
"Itzik Ben-Gan" wrote:

> Luis, try:
> SELECT ID, Item
> , MAX(CASE WHEN pos = 1 THEN Feature END) AS F1
> , MAX(CASE WHEN pos = 2 THEN Feature END) AS F2
> , MAX(CASE WHEN pos = 3 THEN Feature END) AS F3
> /* add more items here */
> FROM (SELECT F.ID, I.Description AS Item, F.Description AS Feature,
> (SELECT COUNT(*)
> FROM tblFeatures AS F2
> WHERE F2.ID = F.ID
> AND F2.Description <= F.Description) AS Pos
> FROM tblItems AS I
> JOIN tblFeatures AS F
> ON F.ID = I.ID) AS D
> GROUP BY ID, Item;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
> Anything written in this message represents my view, my own view, and
> nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
>
> "LUIS" <LUIS@.discussions.microsoft.com> wrote in message
> news:6D153B9E-5373-49B3-8E27-AD6B69F27B35@.microsoft.com...
>
>|||Yes you can walk..but if you want to ride check out Rac:
for dynamic crosstabs.(See the @.rank parameter).
www.rac4sql.net|||Dear Itzik:
Here, once again, Luis. Sorry! I was in a hurry when I saw your answer and
implemented it as soon as I can in my application. I have noted that you are
a very experimented developer, and very succeful too. Thank you very much fo
r
your time dedicated to me. I did not finish sorprised by the query you gave
me, it is extraordinary by utility and simplicity. I wonder for some
literature to learn more about, but I already access your links and see your
books. Nothing more to say. I hope to keep in touch with you and increase
knowledge.
Once again, thank you very much
Luis Garcia
IT Consultant
"Itzik Ben-Gan" wrote:

> Luis, try:
> SELECT ID, Item
> , MAX(CASE WHEN pos = 1 THEN Feature END) AS F1
> , MAX(CASE WHEN pos = 2 THEN Feature END) AS F2
> , MAX(CASE WHEN pos = 3 THEN Feature END) AS F3
> /* add more items here */
> FROM (SELECT F.ID, I.Description AS Item, F.Description AS Feature,
> (SELECT COUNT(*)
> FROM tblFeatures AS F2
> WHERE F2.ID = F.ID
> AND F2.Description <= F.Description) AS Pos
> FROM tblItems AS I
> JOIN tblFeatures AS F
> ON F.ID = I.ID) AS D
> GROUP BY ID, Item;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
> Anything written in this message represents my view, my own view, and
> nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
>
> "LUIS" <LUIS@.discussions.microsoft.com> wrote in message
> news:6D153B9E-5373-49B3-8E27-AD6B69F27B35@.microsoft.com...
>
>|||Luis,
No need to apologize; it's my pleasure. Your original thank you was more
than I could handle.
I'm actually lost for words when the subject matter is not SQL; and as
proof, replying to you took me more time then producing a transitive closure
of a graph with a recursive query. ;-)
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"LUIS" <LUIS@.discussions.microsoft.com> wrote in message
news:CB89285B-CD92-4DA5-B91D-AD668FEC358F@.microsoft.com...
> Dear Itzik:
> Here, once again, Luis. Sorry! I was in a hurry when I saw your answer and
> implemented it as soon as I can in my application. I have noted that you
> are
> a very experimented developer, and very succeful too. Thank you very much
> for
> your time dedicated to me. I did not finish sorprised by the query you
> gave
> me, it is extraordinary by utility and simplicity. I wonder for some
> literature to learn more about, but I already access your links and see
> your
> books. Nothing more to say. I hope to keep in touch with you and increase
> knowledge.
> Once again, thank you very much
> --
> Luis Garcia
> IT Consultant
>
> "Itzik Ben-Gan" wrote:
>

Cross Tab Report

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

This is not a crosstab report but an anti-crosstab report. Assuming SQL Server 2005, have you tried the new UNPIVOT function to unpivot the dataset?

Cross tab question

I have a cross tab grid that calculates monies for programs for different months, however some of these programs dont make any money in some months and some do..How do I get it to show the month name with no values instead of having it just omit the column for the month with no values?

thanks

-Jim
http://www.peritussystems.comI struggled with finding the answer to this question as well. In the end I had to create my own crosstab with formulas and groups. It was a pain in the butt, but I'm happier with it now than I would have been if I had gotten the crosstab to work. I have a lot more control over my own "crosstab" creation.

cross tab question

I am trying to create a cross tab query to to combine the rows for two dates
on one line:
DATA:
( query to get this data:
select top 6
stkhstCsiSym,
stkhstDATE,
stkhstXO
from stkhst
where stkhstdate >=20050225
order by stkhstcsisym, stkhstDATE)
1006 20050225 O
1006 20050228 X
1008 20050225 O
1008 20050228 O
1012 20050225 O
1012 20050228 X
I wish the result to be
1006 O X
1008 O O
1012 O X
I've tried the following query that does not work:
select top 6
stkhstCsiSym,
CASE stkhstDate WHEN 20050225 THEN stkhstXO END AS FXO,
CASE stkhstDATE WHEN 20050228 THEN stkhstXO END AS SXO
from stkhst
where stkhstdate >=20050225
group by stkhstCsiSym, stkhstDATE, stkhstXO
order by stkhstcsisym, stkhstDATE:
this gives me :
1006 O NULL
1006 NULL X
1008 O NULL
1008 NULL O
1012 X NULL
1012 NULL O
Other than the fact that it does not work does anyone have anyideas how to
do this correctly?
thanks
kesShould i be using a join instead?
thanks
"Kurt Schroeder" wrote:

> I am trying to create a cross tab query to to combine the rows for two dat
es
> on one line:
> DATA:
> ( query to get this data:
> select top 6
> stkhstCsiSym,
> stkhstDATE,
> stkhstXO
> from stkhst
> where stkhstdate >=20050225
> order by stkhstcsisym, stkhstDATE)
> 1006 20050225 O
> 1006 20050228 X
> 1008 20050225 O
> 1008 20050228 O
> 1012 20050225 O
> 1012 20050228 X
> I wish the result to be
> 1006 O X
> 1008 O O
> 1012 O X
> I've tried the following query that does not work:
> select top 6
> stkhstCsiSym,
> CASE stkhstDate WHEN 20050225 THEN stkhstXO END AS FXO,
> CASE stkhstDATE WHEN 20050228 THEN stkhstXO END AS SXO
> from stkhst
> where stkhstdate >=20050225
> group by stkhstCsiSym, stkhstDATE, stkhstXO
> order by stkhstcsisym, stkhstDATE:
> this gives me :
> 1006 O NULL
> 1006 NULL X
> 1008 O NULL
> 1008 NULL O
> 1012 X NULL
> 1012 NULL O
> Other than the fact that it does not work does anyone have anyideas how to
> do this correctly?
> thanks
> kes|||As a general approach, one could use:
SELECT col1,
MAX( CASE col2 WHEN '20050225'
THEN col3
END ) AS "somecol1",
MAX( CASE col2 WHEN '20050228'
THEN col3
END ) AS "somecol2"
FROM tbl
GROUP BY col1 ;
Anith|||hey! that works!! thanks. I'm using something else a self join, but i'd have
used this if i saw it first.
thank you
kes
"Anith Sen" wrote:

> As a general approach, one could use:
> SELECT col1,
> MAX( CASE col2 WHEN '20050225'
> THEN col3
> END ) AS "somecol1",
> MAX( CASE col2 WHEN '20050228'
> THEN col3
> END ) AS "somecol2"
> FROM tbl
> GROUP BY col1 ;
> --
> Anith
>
>

Cross Tab query in SqlServer

Hi,
I have three tables with there fields given in brackets:
User: [userId as int] (PK), [userName as navarchar]
Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
DivisionsOfUsers: [userId as int],[divisionId as int]
the "DivisionsOfUsers" tables has many-to-many relationships between
userid and divisionId.
I would like to generate a result something like this:
Division1 Division2 Division3
User1 1 0 0
User2 0 0 1
User3 1 1 0
User4 0 0 0
and so on...
where "1" indicates that the given User-Division combination exists and
"0" denotes that it doesnt in the "DivisionOfUsers" table.
I have tried all sorts of joins to get this data. But was unable to do
this.
I have been told that this is possible by a cross-tab query. I dont
know how to generate this query.
Can anybody give me a solution for this to be used in Sqlserver 2000 as
well as Sqlserver 2005.
Thanks in advance
Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Darsin" <darsin@.gmail.com> wrote in message
news:1164563679.582175.136260@.h54g2000cwb.googlegr oups.com...
> Hi,
> I have three tables with there fields given in brackets:
> User: [userId as int] (PK), [userName as navarchar]
> Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
> DivisionsOfUsers: [userId as int],[divisionId as int]
> the "DivisionsOfUsers" tables has many-to-many relationships between
> userid and divisionId.
> I would like to generate a result something like this:
> Division1 Division2 Division3
> User1 1 0 0
> User2 0 0 1
> User3 1 1 0
> User4 0 0 0
> and so on...
> where "1" indicates that the given User-Division combination exists and
> "0" denotes that it doesnt in the "DivisionOfUsers" table.
> I have tried all sorts of joins to get this data. But was unable to do
> this.
> I have been told that this is possible by a cross-tab query. I dont
> know how to generate this query.
> Can anybody give me a solution for this to be used in Sqlserver 2000 as
> well as Sqlserver 2005.
> Thanks in advance
>
|||Please post DDL, Sample Data, Expected Results (based on your Sample Data).
In the meantime, you might look up the PIVOT operator in SQL 2005.
"Darsin" <darsin@.gmail.com> wrote in message
news:1164563679.582175.136260@.h54g2000cwb.googlegr oups.com...
> Hi,
> I have three tables with there fields given in brackets:
> User: [userId as int] (PK), [userName as navarchar]
> Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
> DivisionsOfUsers: [userId as int],[divisionId as int]
> the "DivisionsOfUsers" tables has many-to-many relationships between
> userid and divisionId.
> I would like to generate a result something like this:
> Division1 Division2 Division3
> User1 1 0 0
> User2 0 0 1
> User3 1 1 0
> User4 0 0 0
> and so on...
> where "1" indicates that the given User-Division combination exists and
> "0" denotes that it doesnt in the "DivisionOfUsers" table.
> I have tried all sorts of joins to get this data. But was unable to do
> this.
> I have been told that this is possible by a cross-tab query. I dont
> know how to generate this query.
> Can anybody give me a solution for this to be used in Sqlserver 2000 as
> well as Sqlserver 2005.
> Thanks in advance
>

Cross Tab query in SqlServer

Hi,
I have three tables with there fields given in brackets:
User: [userId as int] (PK), [userName as navarchar]
Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
DivisionsOfUsers: [userId as int],[divisionId as int]
the "DivisionsOfUsers" tables has many-to-many relationships between
userid and divisionId.
I would like to generate a result something like this:
Division1 Division2 Division3
User1 1 0 0
User2 0 0 1
User3 1 1 0
User4 0 0 0
and so on...
where "1" indicates that the given User-Division combination exists and
"0" denotes that it doesnt in the "DivisionOfUsers" table.
I have tried all sorts of joins to get this data. But was unable to do
this.
I have been told that this is possible by a cross-tab query. I dont
know how to generate this query.
Can anybody give me a solution for this to be used in Sqlserver 2000 as
well as Sqlserver 2005.
Thanks in advanceOften, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Darsin" <darsin@.gmail.com> wrote in message
news:1164563679.582175.136260@.h54g2000cwb.googlegroups.com...
> Hi,
> I have three tables with there fields given in brackets:
> User: [userId as int] (PK), [userName as navarchar]
> Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
> DivisionsOfUsers: [userId as int],[divisionId as int]
> the "DivisionsOfUsers" tables has many-to-many relationships between
> userid and divisionId.
> I would like to generate a result something like this:
> Division1 Division2 Division3
> User1 1 0 0
> User2 0 0 1
> User3 1 1 0
> User4 0 0 0
> and so on...
> where "1" indicates that the given User-Division combination exists and
> "0" denotes that it doesnt in the "DivisionOfUsers" table.
> I have tried all sorts of joins to get this data. But was unable to do
> this.
> I have been told that this is possible by a cross-tab query. I dont
> know how to generate this query.
> Can anybody give me a solution for this to be used in Sqlserver 2000 as
> well as Sqlserver 2005.
> Thanks in advance
>|||Please post DDL, Sample Data, Expected Results (based on your Sample Data).
In the meantime, you might look up the PIVOT operator in SQL 2005.
"Darsin" <darsin@.gmail.com> wrote in message
news:1164563679.582175.136260@.h54g2000cwb.googlegroups.com...
> Hi,
> I have three tables with there fields given in brackets:
> User: [userId as int] (PK), [userName as navarchar]
> Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
> DivisionsOfUsers: [userId as int],[divisionId as int]
> the "DivisionsOfUsers" tables has many-to-many relationships between
> userid and divisionId.
> I would like to generate a result something like this:
> Division1 Division2 Division3
> User1 1 0 0
> User2 0 0 1
> User3 1 1 0
> User4 0 0 0
> and so on...
> where "1" indicates that the given User-Division combination exists and
> "0" denotes that it doesnt in the "DivisionOfUsers" table.
> I have tried all sorts of joins to get this data. But was unable to do
> this.
> I have been told that this is possible by a cross-tab query. I dont
> know how to generate this query.
> Can anybody give me a solution for this to be used in Sqlserver 2000 as
> well as Sqlserver 2005.
> Thanks in advance
>