?
Solved

CrossTab in sql2005

Posted on 2012-09-13
7
Medium Priority
?
534 Views
Last Modified: 2012-09-13
I am looking for help on a crosstab query. Ideally I would like to run this as a procedure that can take a month and year as parameters.

The query should be simple. The attached ER shows the relationsip. The attached xl doc shows the output.

I need to pivot the data so that the columns along the top are the day value in TxDateTime for a given month and year (as parameters). In the day columns it should show a count of siteTx records and the first column would be the document title from tbl_content. Action would always be filtered for value of 2.

Is this possible in sql 2005?

thanks for any help in advance.
example-output.xlsx
ER.gif
0
Comment
Question by:spanout
7 Comments
 
LVL 12

Expert Comment

by:Jared_S
ID: 38395999
0
 

Expert Comment

by:sqlsree
ID: 38396067
For crosstab best way to use is PIVOT function and it works in 2005..

Refer to the following site
http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
0
 
LVL 41

Expert Comment

by:ralmada
ID: 38396233
try

create procedure pivotsites
(
@month int,
@year int
)
as
select DocumentTitle, [1], [2], [3], [4], [5], [6], [7], .... and so on to [31]
from (
	select 
		a.txID,
		day(a.TxDateTime) as txday, 
		b.DocumentTitle
	from SiteTX a
	inner join tbl_content b on a.txID = b.c_id
	where month(TxDatetime) = @month and year(TxDatetime) = @year
		and a.Action = 2
) a
pivot (count(txID) for txday in ([1], [2], [3], [4], [5], [6], [7], .... and so on, [31])) p

Open in new window

0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38396345
SELECT
    c.DocumentTitle, [1], [2], [3], [4], [5], [6], [7], /* ..., ... */ [31]
FROM (
    SELECT
            txID,
            DAY(TxDateTime) AS txDay
      FROM dbo.SiteTX a
      WHERE          TxDateTime >= CAST(@year AS char(4)) + RIGHT('0' + CAST(@month AS varchar(2)), 2) + '01' AND
          TxDateTime < DATEADD(MONTH, 1, CAST(@year AS char(4)) + RIGHT('0' + CAST(@month AS varchar(2)), 2) + '01') AND
          Action = 2
) base
PIVOT
(
    COUNT(txID) FOR
    txDay IN ( [1], [2], [3], [4], [5], [6], [7], /* ..., ...*/ [31] )
)  AS pvt
INNER JOIN dbo.tblContent c ON
    c.c_id = base.txID
0
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 38396394
you might want to change one of the alias there to avoid issues

create procedure pivotsites
(
@month int,
@year int
)
as
select DocumentTitle, [1], [2], [3], [4], [5], [6], [7], .... and so on to [31]
from (
      select
            a.txID,
            day(a.TxDateTime) as txday,
            b.DocumentTitle
      from SiteTX a
      inner join tbl_content b on a.txID = b.c_id
      where month(TxDatetime) = @month and year(TxDatetime) = @year
            and a.Action = 2
) t1
pivot (count(txID) for txday in ([1], [2], [3], [4], [5], [6], [7], .... and so on, [31])) p
0
 
LVL 12

Expert Comment

by:Jared_S
ID: 38396548
I bet nearly all of these will work. BUT, they all rely on hard-coding the days of the month.
Those days (1-31) will show up in your query results regardless of the number of the days in that particular month.

When this executes, it will execute with ONLY the days where a TxDateTime exists.

declare @month as varchar(2)
declare @year as varchar(4)

set @month = 1
set @year = 2012


declare @sql as NVARCHAR(max)
DECLARE @cols NVARCHAR(2000)
SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' + st.TxDateTime
                        FROM    SiteTx st 
                        WHERE datepart(month,st.TxDateTime) = @month and datepart(year,st.txdatetime) = @year 
                        ORDER BY '],[' + st.TxDateTime
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'
                      
set @sql = '
SELECT * 
FROM
(SELECT  t.DocumentTitle, s.TxDateTime, count(*) as Num
FROM    tbl_content t, SiteTx s 
WHERE t.c_id = s.txID and
datepart(month,st.TxDateTime) = ' + @month + ' and datepart(year,st.txdatetime) = ' +@year + '
 ) p
PIVOT(sum([Num]) FOR TxDateTime IN (' + @cols +  ' )) AS pvt'

print(@sql) /* this will just show you  the final command as a message*/
exec (@sql)

Open in new window

0
 

Author Closing Comment

by:spanout
ID: 38396564
Exactly what I needed. Thanks
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question