table 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

Development Tools

Reporting Services Development

table help

Author

Topic

igooder
Starting Member

1 Post

Posted - 2010-08-16 : 17:19:52

I'm trying to pull a field from a table within a table and i'm having a hard time to get the data formated the way I wanted. I'm writing these codes in excel sql:

Below is my code:

SELECT
"gen"."loan_num",
"impounds"."descriptn",
"impounds"."annual"

FROM "DMD_Data"."dbo"."fun" "fun" INNER JOIN ("DMD_Data"."dbo"."impounds" "impounds" FULL OUTER JOIN "DMD_Data"."dbo"."gen" "gen" ON "impounds"."file_id"="gen"."file_id") ON "fun"."file_id"="gen"."file_id"

The data will return something like this:
Loan # Desc value
123456789 Hazzard $199.99
123456789 Flood $199.99
123456789 City $199.99
123456789 County $199.99

How do I return the values Horizontally, like so:
Loan # Hazzard Flood City County
123456789 $199.99 $199.99 $199.99 $199.99

BTW: the code and tables above is simply to illustrate my problem, my actual report have hundreds of fields and tables.

Thanks!

manub22
Starting Member

6 Posts

Posted - 2010-08-17 : 10:59:01

Try this... it will work:

select 123456789 as id, 'Hazzard' as des, 199.99 as val
into #tempTable
union
select 123456789, 'Flood', 299.99 union
select 123456789, 'City', 399.99 union
select 123456789, 'County', 499.99

select * from #tempTable

select a.id as id, a.val as Hazzard, b.val as Flood, c.val as City, d.val as County
from #tempTable a,#tempTable b,#tempTable c, #tempTable d
where a.id=b.id and b.id=c.id and c.id=d.id
and a.des='Hazzard' and b.des='Flood' and c.des='City' and d.des='County'

drop table #tempTable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-17 : 11:00:16

try using pivot table in excel.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources