please solve this query - 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

please solve this query

Author

Topic

shezad
Starting Member

32 Posts

Posted - 2007-02-21 : 01:43:51

please solve this query

first table second table

loginname categoryid
categoryid categoryname

first table data

nisar (1,2,3,4)

second table data

1 Bearbase
2 Amunation
3 Training
4 Workorder

Now I want cross tab report like that which will show user name nisar has this category

Nisar bearbase amunation traiaing workorder

Kristen
Test

22859 Posts

Posted - 2007-02-21 : 02:27:03

There is some info on Cross Tabs here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=More+Dynamic+CrossTabs

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-21 : 02:29:32

1) Learn to normalize your tables.
2) Try this

-- prepare sample data
declare @first table (username varchar(20), categories varchar(20))

insert	@first
select	'nisar', '1,2,3,4' union all
select	'peso', '1,3' union all
select	'sqlteam', '4'

declare	@second table (categoryid int, categoryname varchar(20))

insert	@second
select	1, 'Bearbase' union all
select	2, 'Amunation' union all
select	3, 'Training' union all
select	4, 'Workorder'

-- show the result
SELECT		f.UserName,
		s.CategoryID,
		s.CategoryName
FROM		@First AS f
INNER JOIN	@Second AS s on ',' + f.Categories + ',' LIKE '%,' + CAST(s.CategoryID AS VARCHAR) + ',%'
ORDER BY	f.UserName,
		s.CategoryName

Peter Larsson
Helsingborg, Sweden

shezad
Starting Member

32 Posts

Posted - 2007-02-21 : 02:38:17

thanks I will try it actually it is complicated airforce data if any problem come i will back
c u

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources