database errors: crosstab

Showing posts with label crosstab. Show all posts

Thursday, March 22, 2012

Crystal Reports Crosstab

Hello Everyone,
Is there anyway I can repeat the row field values for each record in a crosstab report. Currently they only appear once per group.
Regards,
Omar Malikif u want to repeat row label then
u can repeat by checking repeat row label option in customize style tab of cross tab expert.

if u want to repeat field values
use a formula, assign same field to it and use formula in cross tab.

Wednesday, March 7, 2012

Crystal 9 crosstab export to excel (peoplesoft 8.9)

Hi

We are on Crystal 9 (9.2). I am running the report from PeopleSoft 8.9 (People Tools 8.46).

My crystal uses crosstab. When I run the report online (peoplesoft) choosing the output option 'xls', report is generated with the following formatting issues. Please help me if you figured this out already.

1) Report column width is too big. How to reduce it? Same problem with Row height.

2) The label 'Total' for each group is displayed in the column next to it. I want to print this label in the same column.

3) Row headings are repeated on every page. But they are diplayed after one row of data. Is there any way to suppress these two headings that are displayed through out the report.

4) If I have a 3 columns under a comomn group name and if one of those columns has no data, is there a way to suppress that column? Or is there a way to keep the column headers as in their place?

5) column data spans in multiple columns. How do i keep them in one column?

Your help is greatly appreciated.

Thanks
Sreedevihi,

Do u have any idea about how we can import data's from Excel Pivot table to crystal report (cross-tabs).

Thanks|||Sreedevi, read them in CR help file
Also read
http://support.businessobjects.com/

Saturday, February 25, 2012

Crosstab?

Hi,
I run the following query "SELECT * FROM tbl_A", and get the following
result set:
Surname Firstname
Smith John
Smith Richard
Smith Simon
What I want to see in my result set is:
Surname Firstname
Smith John, Richard, Simon
How do I achieve this?
Thanks
Jon Derbyshire
Can you do it in the client? This kind of thing is extremely inefficient in
SQL Server.
If you must do it in SQL Server, refer to the following:
http://www.aspfaq.com/show.asp?id=2529
Adam Machanic
Pro SQL Server 2005, available now
www.apress.com/book/bookDisplay.html?bID=457
"Jon Derbyshire" <JonDerbyshire@.discussions.microsoft.com> wrote in message
news:E476C459-477C-4882-A705-674A19D20198@.microsoft.com...
> Hi,
> I run the following query "SELECT * FROM tbl_A", and get the following
> result set:
> Surname Firstname
> Smith John
> Smith Richard
> Smith Simon
> What I want to see in my result set is:
> Surname Firstname
> Smith John, Richard, Simon
> How do I achieve this?
> Thanks
> Jon Derbyshire
|||Thats brilliant - Thanks
"Adam Machanic" wrote:

> Can you do it in the client? This kind of thing is extremely inefficient in
> SQL Server.
> If you must do it in SQL Server, refer to the following:
> http://www.aspfaq.com/show.asp?id=2529
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Jon Derbyshire" <JonDerbyshire@.discussions.microsoft.com> wrote in message
> news:E476C459-477C-4882-A705-674A19D20198@.microsoft.com...
>
>

Crosstab?

Hello,
I have this:
Part Month Type
0001A 10/2004 EM
0001A 10/2004 MM
0001A 11/2004 EM
Would like to get to this:
Part Month EM MM
0001A 10/2004 1 1
0001A 11/2004 1 0
thanks
stevehttp://www.aspfaq.com/2462
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"steve9" <steve9@.gmail.com> wrote in message
news:1111523038.812065.303570@.f14g2000cwb.googlegroups.com...
> Hello,
> I have this:
> Part Month Type
> 0001A 10/2004 EM
> 0001A 10/2004 MM
> 0001A 11/2004 EM
> Would like to get to this:
> Part Month EM MM
> 0001A 10/2004 1 1
> 0001A 11/2004 1 0
> thanks
> steve
>|||Try,
select
Part,
[Month],
count(case when Type = 'EM' then 1 end) as EM,
count(case when Type = 'MM' then 1 end) as MM,
from
table1
group by
Part,
[Month];
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
AMB
"steve9" wrote:

> Hello,
> I have this:
> Part Month Type
> 0001A 10/2004 EM
> 0001A 10/2004 MM
> 0001A 11/2004 EM
> Would like to get to this:
> Part Month EM MM
> 0001A 10/2004 1 1
> 0001A 11/2004 1 0
> thanks
> steve
>|||Simple...thx
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E9A4EAE8-A669-49FE-9C99-40125D798F22@.microsoft.com...
> Try,
> select
> Part,
> [Month],
> count(case when Type = 'EM' then 1 end) as EM,
> count(case when Type = 'MM' then 1 end) as MM,
> from
> table1
> group by
> Part,
> [Month];
> HOW TO: Rotate a Table in SQL Server
> http://support.microsoft.com/defaul...574&Product=sql
>
> AMB
> "steve9" wrote:
>|||If anyone is looking for the BMW of crosstabs as opposed
to the Chevy version check out the RAC utility for S2k:)
www.rac4sql.net

Crosstab?

Hi,
I run the following query "SELECT * FROM tbl_A", and get the following
result set:
Surname Firstname
Smith John
Smith Richard
Smith Simon
What I want to see in my result set is:
Surname Firstname
Smith John, Richard, Simon
How do I achieve this?
Thanks
Jon DerbyshireCan you do it in the client? This kind of thing is extremely inefficient in
SQL Server.
If you must do it in SQL Server, refer to the following:
http://www.aspfaq.com/show.asp?id=2529
Adam Machanic
Pro SQL Server 2005, available now
www.apress.com/book/bookDisplay.html?bID=457
--
"Jon Derbyshire" <JonDerbyshire@.discussions.microsoft.com> wrote in message
news:E476C459-477C-4882-A705-674A19D20198@.microsoft.com...
> Hi,
> I run the following query "SELECT * FROM tbl_A", and get the following
> result set:
> Surname Firstname
> Smith John
> Smith Richard
> Smith Simon
> What I want to see in my result set is:
> Surname Firstname
> Smith John, Richard, Simon
> How do I achieve this?
> Thanks
> Jon Derbyshire|||Thats brilliant - Thanks
"Adam Machanic" wrote:

> Can you do it in the client? This kind of thing is extremely inefficient
in
> SQL Server.
> If you must do it in SQL Server, refer to the following:
> http://www.aspfaq.com/show.asp?id=2529
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Jon Derbyshire" <JonDerbyshire@.discussions.microsoft.com> wrote in messag
e
> news:E476C459-477C-4882-A705-674A19D20198@.microsoft.com...
>
>

crosstab?

I've made "crosstab" queries in Access - it this doable in sql'
My table has:
ID
TestNumber
Score
Each user can have 1-3 records, one for each of the 3 tests.
I need my output to look like this:
ID Test1 Test2 Test3
--
1 score1 score2 score3
2 score1
3 score1 score2
(not everyone will have taken the 2nd or 3rd test at the same time; score#
just indicates the testscore)
'
Thanks for any help or pointers.
-RYes, but it must be statically defined like so:
Select Id
, Min(Case When TestNumber = 1 Then Score End) As Test1
, Min(Case When TestNumber = 2 Then Score End) As Test2
, Min(Case When TestNumber = 3 Then Score End) As Test3
From TableName
Group By Id
Thomas
"r" <r@.r.com> wrote in message news:uB0xq6%23XFHA.2768@.tk2msftngp13.phx.gbl...d">
> I've made "crosstab" queries in Access - it this doable in sql'
> My table has:
> ID
> TestNumber
> Score
> Each user can have 1-3 records, one for each of the 3 tests.
> I need my output to look like this:
> ID Test1 Test2 Test3
> --
> 1 score1 score2 score3
> 2 score1
> 3 score1 score2
> (not everyone will have taken the 2nd or 3rd test at the same time; score#
> just indicates the testscore)
> '
> Thanks for any help or pointers.
> -R
>
>|||SELECT id,
SUM(CASE WHEN testnumber = 1 THEN score END) AS test1,
SUM(CASE WHEN testnumber = 2 THEN score END) AS test2,
SUM(CASE WHEN testnumber = 3 THEN score END) AS test3
FROM YourTable
GROUP BY id
David Portas
SQL Server MVP
--|||If you ever need to go beyond xtab 101 check out
the powerful RAC utility.Similar in concept to Access xtab
but goes way beyond with its options and features.Also
includes other functionality (ie. ranking options) made easy.
www.rac4sql.net

Crosstab. 2 totals ?

Hi,

Am using robvolks crosstab-procedure to generate a crosstab query.

I get this result:
Total A B C
juli 455 1 107 347
okt 83 1 9 73
aug 612 1 113 498
juni 451 1 108 342

So I get a total for each month. But I would also like a total of each
letter
Total A B C
juli 455 1 107 347
okt 83 1 9 73
aug 612 1 113 498
juni 451 1 108 342
Total 1601 4 337 1260

Is that possible?

/jim
--call to procedure
execute crosstab 'select DATENAME(month,(theDate)) as '' '', count(*) as
'MonthsTotal'' from tblData group by
DATENAME(month,(theDate))','count(letter)','letter ','tblData'

----Robvolks procedure--
CREATE PROCEDURE crosstab
@.select varchar(8000),
@.sumfunc varchar(100),
@.pivot varchar(100),
@.table varchar(100),
@.where varchar(1000)='1=1'

DECLARE @.sql varchar(8000), @.delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET LANGUAGE Danish

EXEC ('SELECT ' + @.pivot + ' AS pivot INTO ##pivot FROM ' + @.table + ' WHERE
1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' + @.table +
' WHERE '
+ @.where + ' AND ' + @.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
GOJim Andersen (jba020@.politiSLET.dk.invalid) writes:

Quote:

Originally Posted by

Am using robvolks crosstab-procedure to generate a crosstab query.
>
I get this result:
Total A B C
juli 455 1 107 347
okt 83 1 9 73
aug 612 1 113 498
juni 451 1 108 342
>
So I get a total for each month. But I would also like a total of each
letter
Total A B C
juli 455 1 107 347
okt 83 1 9 73
aug 612 1 113 498
juni 451 1 108 342
Total 1601 4 337 1260
>
Is that possible?

You would use INSERT EXEC to capture the result from the crosstab
procedure into a temp table, and then compute a total row from the
data in it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||SELECT month_name,
COUNT(*) AS tot,
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar
GROUP BY month_name
UNION ALL
SELECT 'All months',
COUNT(*) AS tot
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar;

But why not use a report writer in the front end, like you are supposed
to?|||--CELKO-- wrote:

Quote:

Originally Posted by

SELECT month_name,
COUNT(*) AS tot,
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar
GROUP BY month_name
UNION ALL
SELECT 'All months',
COUNT(*) AS tot
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar;
>
But why not use a report writer in the front end, like you are
supposed to?

So I don't have to hardcode my A, B and C's ?
Because I am using Visual Studio .NET and that leaves me with Crystal
Reports (yuckk, hark, spit) as a reporting tool.

I think I will try Erlands suggestion.

/jim

Crosstab, Pivot Query representation

Hello,

I need help with data representation.

I have a query :

SELECT USERLOGIN, SOURCE, DBUSERNAME FROM TBL_USER
WHERE USERLOGIN LIKE 'Don Crilly'

The above query returns the following results:
USERLOGIN SOURCE DBUSERNAME

Don Crilly FC8 Don Crilly
Don Crilly ACT Donald Crilly
Don Crilly SFS Don Crilly

I need the output in following format:

USERLOGIN ACT FC8 SFS
-
Don Crilly Donald Crilly Don Crilly Don Crilly

Can you please guide me as to what I should do to achive the required results.

Thanks.

If you use SQL Server 2005:
SELECT * FROM (

SELECT USERLOGIN, SOURCE, DBUSERNAME FROM TBL_USER
WHERE USERLOGIN LIKE 'Don Crilly'

) p
PIVOT (MIN(DBUSERNAME) FOR SOURCE IN ([FC8], [ACT], [SFS])) pvt
--Or You can use the following for earlier versions
select a.USERLOGIN
, min(case a.SOURCE when 'FC8' then DBUSERNAME end) as [FC8]
, min(case a.SOURCE when 'ACT' then DBUSERNAME end) as [ACT]
, min(case a.SOURCE when 'SFS' then DBUSERNAME end) as [SFS]
from (

SELECT USERLOGIN, SOURCE, DBUSERNAME FROM TBL_USER
WHERE USERLOGIN LIKE 'Don Crilly'

) as a
group by a.USERLOGIN|||

If you're using SQL Server 2005, then you can use the PIVOT operator to do this.

For example, this will work for your query:

SELECT *

FROM (SELECT USERLOGIN, SOURCE, DBUSERNAME

FROM TBL_USER

WHERE USERLOGIN LIKE 'Don Crilly'

) SOURCEQUERY

PIVOT (

MIN(DBUSERNAME)

FOR SOURCE IN ([ACT], [FC8], [SFS])

) AS PIVOTTABLE

The restrictions for using the PIVOT operator are that the PIVOT clause must include an aggregate operator (the MIN in this case, which will work unless a single USERLOGIN and SOURCE combination can have multiple DBUSERNAMEs associated with it) and that the resulting column names (ACT, FC8 and SFS) must be listed explicitly (unlike MS Excel, for example, which just creates columns for every value in the pivotting column).

Hope that gives you a start at least.

Iain

|||

This query will do..

Select * from dbo.TBL_USER
PIVOT
(
Min(DBUserName) For Source in ([FC8],[ACT],[SFS])
) as T
Where UserLogin = 'Don Crilly'

|||Thanks a lot :)

CrossTab(?) Query Problem

I've got a fairly tricky report to produce, so I'm trying to build it up bit
by bit, but I've fallen at the first hurdle!
I need to produce a view that lists all PartNo's and for each, what quantity
is in what location, e.g.
PartNo, GoodsIn, PostInspection, Quarantine, Stock, Total
WE12024, 5, null, 3, 10, 18
WE12067, 1, 1, null, 5, 7
etc...
I have the SQL to extract each bit of the data, but the problem is
presenting it in this way.
SQL:
Select PartNo, Count(*) as InGoodsIn
from Filters
Where LocationID = 15 /* for GoodsIn */
The PostInspection, Quarrantine & Stock locations have the following
LocationIDs - 16, 14, 17
How do I combine these result into one structure as indicated? Can somebody
point me in the right direction,please?
[In case it isn't clear from the above, the Filters table has this
structure: SerialNo (primary key), PartNo, LocationID]
Thanks in advance
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:%237q365qlFHA.360@.TK2MSFTNGP09.phx.gbl...
> I've got a fairly tricky report to produce, so I'm trying to build it up
> bit by bit, but I've fallen at the first hurdle!
> I need to produce a view that lists all PartNo's and for each, what
> quantity is in what location, e.g.
> PartNo, GoodsIn, PostInspection, Quarantine, Stock, Total
> WE12024, 5, null, 3, 10, 18
> WE12067, 1, 1, null, 5, 7
> etc...
> I have the SQL to extract each bit of the data, but the problem is
> presenting it in this way.
> SQL:
> Select PartNo, Count(*) as InGoodsIn
> from Filters
> Where LocationID = 15 /* for GoodsIn */
> The PostInspection, Quarrantine & Stock locations have the following
> LocationIDs - 16, 14, 17
> How do I combine these result into one structure as indicated? Can
> somebody point me in the right direction,please?
> [In case it isn't clear from the above, the Filters table has this
> structure: SerialNo (primary key), PartNo, LocationID]
> Thanks in advance
>
Apologies - problem solved.
I was on the right lines but had made a couple of errors. The solution I
have is:
SELECT Partno,
[GoodsIn] = SUM(CASE LocationID WHEN 15 THEN 1 ELSE 0 END),
[PostInspection] = SUM(CASE LocationID WHEN 16 THEN 1 ELSE 0 END),
[Quarantine] = SUM(CASE LocationID WHEN 14 THEN 1 ELSE 0 END),
[Stock] = SUM(CASE LocationID WHEN 17 THEN 1 ELSE 0 END)
FROM Filters
GROUP BY PartNo|||Try,
select
PartNo,
sum(case when location = 15 then 1 else 0 end) as GoodsIn,
sum(case when location = 16 then 1 else 0 end) as PostInspection,
sum(case when location = 14 then 1 else 0 end) as Quarrantine ,
sum(case when location = 17 then 1 else 0 end) as Stock
from
Filters
order by
PartNo
go
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
AMB
"CJM" wrote:

> I've got a fairly tricky report to produce, so I'm trying to build it up b
it
> by bit, but I've fallen at the first hurdle!
> I need to produce a view that lists all PartNo's and for each, what quanti
ty
> is in what location, e.g.
> PartNo, GoodsIn, PostInspection, Quarantine, Stock, Total
> WE12024, 5, null, 3, 10, 18
> WE12067, 1, 1, null, 5, 7
> etc...
> I have the SQL to extract each bit of the data, but the problem is
> presenting it in this way.
> SQL:
> Select PartNo, Count(*) as InGoodsIn
> from Filters
> Where LocationID = 15 /* for GoodsIn */
> The PostInspection, Quarrantine & Stock locations have the following
> LocationIDs - 16, 14, 17
> How do I combine these result into one structure as indicated? Can somebod
y
> point me in the right direction,please?
> [In case it isn't clear from the above, the Filters table has this
> structure: SerialNo (primary key), PartNo, LocationID]
> Thanks in advance
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:191A665E-2D8E-43F0-845D-EDAAAD54C844@.microsoft.com...
> Try,
> select
> PartNo,
> sum(case when location = 15 then 1 else 0 end) as GoodsIn,
> sum(case when location = 16 then 1 else 0 end) as PostInspection,
> sum(case when location = 14 then 1 else 0 end) as Quarrantine ,
> sum(case when location = 17 then 1 else 0 end) as Stock
> from
> Filters
> order by
> PartNo
> go
>
Curious...
Your syntax is different to mine but still works - eg. "Case when LocationID
= 15" rather than "Case LocationID when 15"
Is this difference significant?
Chris|||The result of both expressions are the same. See CASE in BOL to read more
about "simple case" and "search case" formats.
AMB
"CJM" wrote:

> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:191A665E-2D8E-43F0-845D-EDAAAD54C844@.microsoft.com...
> Curious...
> Your syntax is different to mine but still works - eg. "Case when Location
ID
> = 15" rather than "Case LocationID when 15"
> Is this difference significant?
> Chris
>
>

crosstab(?) query

I am trying to do a query on the following data:
Acct Units Person
10 5 Bob
10 6 Ted
10 3 Sam
11 4 Jill
12 4 Mary
I want my recordset to look like:
Acct 1 2 3 . . . .
10 Ted Bob Sam
11 Jill
12 Mary
In other words, my columns would be 1st, 2nd, 3rd, etc... for each
person/account, based on units.
I currently do this by dumping my data in Excel, doing some manipulations,
exporting up into Access and doing a crosstab query. I am hoping I can do
this in one process in SQL. Can someone point me in the right direction?
thanks
Dion
Have a look here:
http://www.aspfaq.com/show.asp?id=2462
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Dion" <Dion@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F2BE3DD9-41BC-4001-8E81-677731C4569A@.microsoft.com...
>I am trying to do a query on the following data:
> Acct Units Person
> 10 5 Bob
> 10 6 Ted
> 10 3 Sam
> 11 4 Jill
> 12 4 Mary
> I want my recordset to look like:
> Acct 1 2 3 . . . .
> 10 Ted Bob Sam
> 11 Jill
> 12 Mary
> In other words, my columns would be 1st, 2nd, 3rd, etc... for each
> person/account, based on units.
> I currently do this by dumping my data in Excel, doing some manipulations,
> exporting up into Access and doing a crosstab query. I am hoping I can do
> this in one process in SQL. Can someone point me in the right direction?
> thanks
> Dion

crosstab(?) query

I am trying to do a query on the following data:
Acct Units Person
10 5 Bob
10 6 Ted
10 3 Sam
11 4 Jill
12 4 Mary
I want my recordset to look like:
Acct 1 2 3 . . . .
10 Ted Bob Sam
11 Jill
12 Mary
In other words, my columns would be 1st, 2nd, 3rd, etc... for each
person/account, based on units.
I currently do this by dumping my data in Excel, doing some manipulations,
exporting up into Access and doing a crosstab query. I am hoping I can do
this in one process in SQL. Can someone point me in the right direction?
thanks
DionHave a look here:
http://www.aspfaq.com/show.asp?id=2462
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Dion" <Dion@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F2BE3DD9-41BC-4001-8E81-677731C4569A@.microsoft.com...
>I am trying to do a query on the following data:
> Acct Units Person
> 10 5 Bob
> 10 6 Ted
> 10 3 Sam
> 11 4 Jill
> 12 4 Mary
> I want my recordset to look like:
> Acct 1 2 3 . . . .
> 10 Ted Bob Sam
> 11 Jill
> 12 Mary
> In other words, my columns would be 1st, 2nd, 3rd, etc... for each
> person/account, based on units.
> I currently do this by dumping my data in Excel, doing some manipulations,
> exporting up into Access and doing a crosstab query. I am hoping I can do
> this in one process in SQL. Can someone point me in the right direction?
> thanks
> Dion

crosstab(?) query

I am trying to do a query on the following data:
Acct Units Person
10 5 Bob
10 6 Ted
10 3 Sam
11 4 Jill
12 4 Mary
I want my recordset to look like:
Acct 1 2 3 . . . .
10 Ted Bob Sam
11 Jill
12 Mary
In other words, my columns would be 1st, 2nd, 3rd, etc... for each
person/account, based on units.
I currently do this by dumping my data in Excel, doing some manipulations,
exporting up into Access and doing a crosstab query. I am hoping I can do
this in one process in SQL. Can someone point me in the right direction?
thanks
DionHave a look here:
http://www.aspfaq.com/show.asp?id=2462
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Dion" <Dion@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F2BE3DD9-41BC-4001-8E81-677731C4569A@.microsoft.com...
>I am trying to do a query on the following data:
> Acct Units Person
> 10 5 Bob
> 10 6 Ted
> 10 3 Sam
> 11 4 Jill
> 12 4 Mary
> I want my recordset to look like:
> Acct 1 2 3 . . . .
> 10 Ted Bob Sam
> 11 Jill
> 12 Mary
> In other words, my columns would be 1st, 2nd, 3rd, etc... for each
> person/account, based on units.
> I currently do this by dumping my data in Excel, doing some manipulations,
> exporting up into Access and doing a crosstab query. I am hoping I can do
> this in one process in SQL. Can someone point me in the right direction?
> thanks
> Dion

Crosstab woes

Good day all.

I'm using crystal reports 9.

I have a list of names down the left (sDriverName) and the months across the top (liMonth). The summarised field is the Driver score (liScore). There is another database field called liBandColour which indicates what colour band the driver's score is for that month (red, amber, green).

I have a formula on the Background colour of the liScore field (Basic syntax):
if {Rag.liBandColour} =255 then
formula = crRed 'Red
elseif {Rag.liBandColour} =65535 then
formula = rgb(255,255,0) 'Amber
elseif {Rag.liBandColour} =13434828 then
formula = crGreen 'Green
else
formula = crWhite 'White
end if

However this seems to make ALL the scores appear amber and doesn't seem to work on a per record basis.

On to question 2:
The user has now requested to add another column on the right with the YTD kms travelled. Now it's a simple thing to add another summarised field and suppress it (the DB also contains the kms travelled each month so this is easy), but then it appears underneath the averaged driver score total and not next to it in it's own column... Is there anyway around this?
Can I perhaps duplicate the look of my report without using a crosstabFor your first problem, I came thru something like that in CrystalDecisions.com

visit this url (http://support.businessobjects.com/library/kbase/articles/c2006837.asp)

For your second problem, it may not. any way

have a look at here (http://support.businessobjects.com/library/kbase/articles/c2006237.asp)

crosstab stored procedure

I have a table that has the following information like this:

Name Date 1st item 2nd item 3rd item 4th item 5th item
Defaul name 11/1/2005 100 0 0 0 0
Defaul name 11/2/2005 100 0 0 0 0
Defaul name 11/3/2005 99.69 0 0 0 0

I need to create a crosstab query (NOT using crystal report) that will display the information like:

11/1/2005 11/2/2005 11/3/2005
1st item 100.00 100.00 99.69
2nd item 0.00 0.00 0.00
3rd item 0.00 0.00 0.00
4th item 0.00 0.00 0.00
5th item 0.00 0.00 0.00

I have never used a crosstab query in sql server before. Please help!First, look up CROSSTAB in Books Online and you will see the general method for handling this using CASE statements.
Unfortunately you are going to have problems if your column headers are dynamic, which is often the case when you are grouping your columns by date values.
If, as your example implies, you are only dealing with one year's worth of data at a time, then you can group your columns by datepart(month...), which will give you 12 consistent column labels.
Avoid dynamic crosstab queries if at all possible (or until you upgrade to SQL Server 2005...). And though you apparently already know this, I have to say that dynamic crosstab functionality does not really belong in SQL server anyway, SQL Server 2005 not withstanding. It is a presentation issue.|||Thanks for your reply. Unfortunately, the column values have to be dynamic. There is an easy way to present this in crystal reports but unfortunately i have to write directly to an excel file from a stored procedure. I'm at a loss right now as to how to accomplish this presentation in a stored procedure with dynamic columns.|||Not to beat a dead horse, but look at ags crosstab and/or RAC (used to be replacement for access crosstab, but they changed their name).

Regards,

hmscott

Friday, February 24, 2012

Crosstab result

Hi there,

I'm using sql2005. I've got a table in tabular form and I need to convert it to crosstab format. I've tried to use pivot operator but i can't get the result i want. Because pivot operator needs to aggregate a field but what i need is Dim values as column names and DimCode as values within those columns as it is, no aggregations.

For example : This is tabular table.

Dim DimCode JrnlEntry GLDistId TrxDate GLPostDT TrxType AccountIndex DebitAmt CreditAmt Amount 4 2 54222 1 25/05/2007 25/05/2007 1 44256 617 0 617 4 11991 54222 1 25/05/2007 25/05/2007 1 44256 617 0 617 5 7 54222 1 25/05/2007 25/05/2007 1 44256 617 0 617 5 5 54222 1 25/05/2007 25/05/2007 1 44256 617 0 617 4 1 54225 1 31/05/2007 31/05/2007 1 44256 600 0 600 5 5 54225 1 31/05/2007 31/05/2007 1 44256 600 0 600 4 12821 54225 2 31/05/2007 31/05/2007 1 44256 400 0 400 5 4 54225 2 31/05/2007 31/05/2007 1 44256 400 0 400 4 3 54227 3 31/05/2007 31/05/2007 1 44256 0 500 -500 5 8 54227 3 31/05/2007 31/05/2007 1 44256 0 500 -500

And i want the end result as under..

jrnentry aaGLDistID TRXDATE GLPOSTDT aaTRXType Dim1 Dim2 Dim3 Dim4 Dim5 Dim6 Dim7 Dim8 Dim9 Dim10 ACTINDX DEBITAMT CRDTAMNT Amount 54222 1 25/05/2007 25/05/2007 1 0 0 0 2 7 0 0 0 0 0 44256 617 0 617 54222 1 25/05/2007 25/05/2007 1 0 0 0 11991 5 0 0 0 0 0 44256 617 0 617 54225 1 31/05/2007 31/05/2007 1 0 0 0 1 5 0 0 0 0 0 44256 600 0 600 54225 2 31/05/2007 31/05/2007 1 0 0 0 12821 4 0 0 0 0 0 44256 400 0 400 54227 3 31/05/2007 31/05/2007 1 0 0 0 3 8 0 0 0 0 0 44256 0 500 -500

Your help will much appreciated.

There is an excellent article about PIVOT by Peter Larsson.

Follow the link :

Pivot table for Microsoft SQL Server

Thanks

Naras.

|||

For quite a few examples of using PIVOT, do a search on this Forum for the keyword 'Pivot'. Amazing how often this question is asked, and how often folks don't seem to bother looking in the archives to find their answer.

|||

Thanks Naras,

I'm not looking for help in Pivot functionality of sql2005 as it won't be usefull for me. If you look at the first table, i want the first column Dim to be used for the new columns i.e. Dim1, Dim2, Dim3 will be columns in the crosstab result but then i want DimCode to appear under respective columns without any aggregations. In pivot you've to use aggregate function. Also in crosstab result if look at the record with jrnentry 54222, there are two lines with the same aaGLDistId i.e. i don't want to aggregate values within dim1, dim2 etc.

Thanks

Vivek

|||

Vivek,

You are wrong. You can achive it using the PIVOT Operator. See the below query..

Code Snippet

Create Table #data (

[Dim] int ,

[DimCode] int ,

[JrnlEntry] int ,

[GLDistId] int ,

[TrxDate] datetime ,

[GLPostDT] datetime ,

[TrxType] int ,

[AccountIndex] int ,

[DebitAmt] int ,

[CreditAmt] int ,

[Amount] int

);

Set Dateformat DMY

Insert Into #data Values('4','2','54222','1','25/05/2007','25/05/2007','1','44256','617','0','617');

Insert Into #data Values('4','11991','54222','1','25/05/2007','25/05/2007','1','44256','617','0','617');

Insert Into #data Values('5','7','54222','1','25/05/2007','25/05/2007','1','44256','617','0','617');

Insert Into #data Values('5','5','54222','1','25/05/2007','25/05/2007','1','44256','617','0','617');

Insert Into #data Values('4','1','54225','1','31/05/2007','31/05/2007','1','44256','600','0','600');

Insert Into #data Values('5','5','54225','1','31/05/2007','31/05/2007','1','44256','600','0','600');

Insert Into #data Values('4','12821','54225','2','31/05/2007','31/05/2007','1','44256','400','0','400');

Insert Into #data Values('5','4','54225','2','31/05/2007','31/05/2007','1','44256','400','0','400');

Insert Into #data Values('4','3','54227','3','31/05/2007','31/05/2007','1','44256','0','500','-500');

Insert Into #data Values('5','8','54227','3','31/05/2007','31/05/2007','1','44256','0','500','-500');

Select

[JrnlEntry]

,[GLDistId]

,[TrxDate]

,[GLPostDT]

,[TrxType]

,Isnull([1],0) Dim1

,Isnull([2],0) Dim2

,Isnull([3],0) Dim3

,Isnull([4],0) Dim4

,Isnull([5],0) Dim5

,Isnull([6],0) Dim6

,Isnull([7],0) Dim7

,Isnull([8],0) Dim8

,Isnull([9],0) Dim9

,Isnull([10],0) Dim10

,[AccountIndex]

,[DebitAmt]

,[CreditAmt]

,[Amount] From

(

Select

[Dim]

,[DimCode]

,[GLDistId]

,[JrnlEntry]

,[TrxDate]

,[GLPostDT]

,[TrxType]

,[AccountIndex]

,[DebitAmt]

,[CreditAmt]

,[Amount]

,Row_Number() Over (Partition By [Dim],[GLDistId],[JrnlEntry] Order By [Dim],[GLDistId],[JrnlEntry]) RowId

From

#data

) as Data

Pivot

(

Max([DimCode])

For [Dim] in

(

[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]

)

) As PVT

|||

Thanks Manivannan, much appreciated. Its returning correct result. I almost spent the whole day on this yesterday with frustration.

Regards,

Vivek

Crosstab Report

I am creating report with total costs by job, it repeats "column totals"
two times, and at the end column "grand total". "grand total" column is okay.
Is there any way I can drop/disable repeating "column totals". See example below, help me to drop repeating column.

Thanks

Job 1 Job 2 GrandTotal
Total Total

Cost 1 177,855 177,855 162,755 162,755 340,610
Cost 2 169,847 169,847 161,063 161,063 330,910
Cost 3 37,404 37,404 102,416 102,416 139,820In CR,right click on the cross tabl. Choose format Cross tabl. U ve a tab like "Customize Style". Choose each and every row in Rows List and below that u ve Grouping Option. Suppres that grouping option. I think this may be a soln. Try it out.

Crosstab question for a "properties" table

Most of the examples for crosstabs use pivot. It looks like aggregations are what they are used for. What about if I have a dynamic properties table. So there is a table Person and a table Property and a table PropertyPersonValue. Is there any way to create a crosstab table of Person with Properties listed in the column heading and the Values in the cells?

Sure, can you post some DDL and sample data?

AMB

|||

CREATE TABLE Person

(

PersonID INT IDENTITY(1,1) NOT NULL,

[Name] VARCHAR(200),

CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED (PersonID)

)

CREATE TABLE PersonProperty

(

PersonPropertyID INT IDENTITY(1,1) NOT NULL,

[Name] VARCHAR(200),

CONSTRAINT [PK_PersonProperty] PRIMARY KEY CLUSTERED (PersonPropertyID)

)

CREATE TABLE PersonPropertyValue

(

PersonID INT NOT NULL,

PersonPropertyID INT NOT NULL,

[Value] VARCHAR(1000),

CONSTRAINT [PK_PersonPropertyValue] PRIMARY KEY CLUSTERED (PersonID,PersonPropertyID),

CONSTRAINT [FK_PersonPropertyValue_PersonID] FOREIGN KEY (PersonID)

REFERENCES [Person],

CONSTRAINT [FK_PersonPropertyValue_PersonPropertyID] FOREIGN KEY (PersonPropertyID)

REFERENCES [PersonProperty],

)

INSERT INTO PersonProperty ([Name]) VALUES ('Eye Color')

INSERT INTO PersonProperty ([Name]) VALUES ('Title')

INSERT INTO Person ([Name]) VALUES ('Joe Smith')

INSERT INTO Person ([Name]) VALUES ('Fred Thompson')

INSERT INTO Person ([Name]) VALUES ('Sally Hamilton')

INSERT INTO PersonPropertyValue (PersonID,PersonPropertyID,[Value])

SELECT p.PersonID,pp.PersonPropertyID,'Brown'