Crosstab query help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Crosstab query help

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-05-15 : 04:51:00

hi all
i have to display columns as row wise and that rows in columns...

e g
FieldVal FieldName
--------------------------------
naitik FirstName
shah LastName
testcomp Company
stpt Address

output should like..
FirstName LastName Company Address
naitk shah testcomp stpt

..
thanks in advance

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-15 : 05:03:42

tons of posts that address exactly this problem

Do a search, try something and if you can't get it to work post what you've tried.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-05-15 : 05:13:06

hi charlie,
i have tried crosstab query in which there are sum of any column exists...
but in my case there is no sum of any column...

i want the Fieldvalues for appropriate Fieldtitles.. as i have shown.

thanks for your reply

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-15 : 05:39:55

use min() or max(). These works on string

KH
[spoiler]Time is always against us[/spoiler]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-15 : 06:22:39

The case statement(s) look like this


MAX(CASE WHEN [fieldName] = 'FirstName' THEN [fieldVal] ELSE '' END) AS [FirstName]

etc

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-05-19 : 06:32:10

can any one help me out on this ?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-19 : 08:06:31

We did:

Here is the spoon fed version:




DECLARE @foo TABLE (
	[fieldVal] NVARCHAR(255)
	, [fieldName] NVARCHAR(255)
	)

INSERT @foo ([fieldVal], [fieldName])
      SELECT 'naitik', 'FirstName'
UNION SELECT 'shah', 'LastName'
UNION SELECT 'testcomp', 'Company'
UNION SELECT 'stpt', 'Address'

SELECT * FROM @foo

SELECT
	MAX(CASE WHEN [fieldName] = 'FirstName' THEN [fieldVal] ELSE '' END) AS [FirstName]
	, MAX(CASE WHEN [fieldName] = 'LastName' THEN [fieldVal] ELSE '' END) AS [LastName]
FROM
	@foo

You can add the other two MAX(CASE... expressions.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-05-19 : 11:31:42

thanking you

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-19 : 11:42:49

Of course -- you'll need to GROUP BY something unique to an individual in your real table -- You've only posted data for one person.......

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-20 : 05:49:00

if ur using 2005 and above use pivot too


SELECT
	 [FirstName], [LastName],[Address],[Company]
FROM 	@foo
PIVOT (MAX(fieldval) FOR fieldname IN ( [FirstName], [LastName],[Address],[Company]))p

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-05-20 : 08:56:38

i am using sql 2005 and 2000 both but above db in sql 2000.

thanks...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources