Help with creating report - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with creating report

Author

Topic

dirtyleeds
Starting Member

3 Posts

Posted - 2008-05-08 : 01:03:30

Hi,

I have a table of data that looks something like: -

County Name Sport
----- ----- -----
Kent Fred Cycling
Kent Fred Hockey
Kent Fred Football
Kent Fred Squash
Surrey Anne Hockey
Surrey Anne Squash
Surrey Anne Netball
Surrey Tara Cycling
Surrey Tara Squash

I am looking to present the data in a tabular format where I would have the data laid out something like: -

County Name Cycling Hockey Football Squash Netball
------ ----- ----- ------ ------ ---- -----
Surrey Fred Yes Yes Yes Yes No
Kent Anne No Yes No Yes Yes
Tara Yes No No Yes No

Where I group by county and then use the unique records from the sport column to dynamically create the headings

All help greatly appreciated

eralper
Yak Posting Veteran

66 Posts

Posted - 2008-05-08 : 02:12:44

Hello,

Can you try this script.

Eralper
[url]http://www.kodyaz.com[/url]

declare @sql nvarchar(max)

select @sql = 'create table ey_report_temp (
county nvarchar(10),
name nvarchar(10), ' +

STUFF(
(

SELECT distinct ',' + sport + ' nvarchar(10) '
FROM ey_report
FOR XML PATH('')

), 1, 1, '')

+

')'

--select @sql
exec sp_executesql @sql
select * from ey_report_temp

DECLARE @county nvarchar(max)
DECLARE @name nvarchar(max)
DECLARE @sport nvarchar(max)

DECLARE merge_cursor CURSOR FAST_FORWARD
FOR
SELECT county, name, sport FROM ey_report

OPEN merge_cursor

FETCH NEXT FROM merge_cursor INTO @county, @name, @sport

WHILE @@FETCH_STATUS = 0
BEGIN
select @sql = N'
update ey_report_temp
set ' + @sport + ' = ''YES''
where
county = ''' + @county + '''
and name = ''' + @name + ''''
print @sql
exec sp_executesql @sql
if @@ROWCOUNT = 0
begin
select @sql = N'
insert into ey_report_temp (
county, name, ' + @sport + '
) values ( ''' +
@county + ''', ''' + @name + ''', ''' + @sport + '''
)'

exec sp_executesql @sql
end

FETCH NEXT FROM merge_cursor INTO @county, @name, @sport
END

CLOSE merge_cursor
DEALLOCATE merge_cursor

select * from ey_report_temp

drop table ey_report_temp

-------------
Eralper
http://www.kodyaz.com

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-08 : 02:14:29

If you are using SQL 2005, look up PIVOT, there is no need for a cursor.

Although your results look incorrect for a start.

dirtyleeds
Starting Member

3 Posts

Posted - 2008-05-08 : 05:57:53

Thanks for this just trying it out and I get "Incorrect syntax near 'XML'

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-08 : 06:36:47

quote:
Originally posted by dirtyleeds

Hi,

I have a table of data that looks something like: -

County Name Sport
----- ----- -----
Kent Fred Cycling
Kent Fred Hockey
Kent Fred Football
Kent Fred Squash
Surrey Anne Hockey
Surrey Anne Squash
Surrey Anne Netball
Surrey Tara Cycling
Surrey Tara Squash

I am looking to present the data in a tabular format where I would have the data laid out something like: -

County Name Cycling Hockey Football Squash Netball
------ ----- ----- ------ ------ ---- -----
Surrey Fred Yes Yes Yes Yes No
Kent Anne No Yes No Yes Yes
Tara Yes No No Yes No

Where I group by county and then use the unique records from the sport column to dynamically create the headings

All help greatly appreciated

Your sample data & o/p doesnt look to be in Sync. According to Sample data Fred is under Kent County but in o/p you have showed him besides Surrey? Why is that?

dirtyleeds
Starting Member

3 Posts

Posted - 2008-05-08 : 06:47:37

My mistake should have read

Kent Fred .......
Surrey Anne .......

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources