Rows to Columns (bit more advance)

Question

Rows to Columns (bit more advance)

renvilo

Mr or Mrs. 500

Points: 522
More actions
April 15, 2011 at 7:09 am

#235787

Hi guys,
I did ask this question before BUT this time it's harder (else I could have done it lol)
I have a database: DB1
There's 2 tables that I will use but let's only talk about the 1 I'm having an issue. Table name is:
AuditDetail
In this database I have data that is being recorded as follow:
AuditDetailIDAuditDetailTypeIDAuditIDTextValueIntegerValueFloatValueLTextValueDateValeCreatedDateModifiedDate
111111NULLNULLNULLtest obj in absNULL59:47.359:47.3
112211NULLNULLNULLtest goal in absNULL59:47.359:47.3
113311NULLNULLNULLtest scope in absNULL59:47.359:47.3
114411NULLNULLNULLtest note in absNULL59:47.359:47.3
115511NULLNULLNULLNULL00:00.059:47.359:47.3
116611NULLNULLNULLNULL00:00.059:47.359:47.3
1177115NULLNULLNULLNULL59:47.359:47.3
118811NULLNULLNULLNULLNULL59:47.359:47.3
119911NULLNULLNULLNULLNULL59:47.359:47.3
1201011NULLNULLNULLNULLNULL59:47.359:47.3
1211111NULL999NULLNULLNULL59:47.359:47.3
(just copy the info to excel to see how the layout is.)
The columns I have is:
AuditDetailID
AuditDetailTypeID
AuditID
TextValue
IntergerValue
FloatValue
LTextValue
DateValue
CreatedDate
ModifiedDate
The createdate and modifieddate I don't really care about so ignore those.
If a record is saved then every text fiend in the program is saved in SQL in a new row. The AuditDetailTypeID has a value from 1 - 11. This is what the textbox is actually called (like say 1= scope, 2=objective and so on.)
I can get it right to move rows to columns if its an integer. But I also have text/memo field and date fields.
Can anyone please help me out. Its a bit urgent because its for my new job 🙂
tx

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

renvilo Mr or Mrs. 500 Points: 522 More actions · Answer 2

Hi WayneS,

sorry about that 🙂 I'll try to provide more detail next time.

I did speak to the dev. of the program and they will map the database to the warehouse database for better reporting next week.

I did come up with the solution of splitting that 1 table into 11 different tables and join them all to get my info in the right way (we need this done by Monday so this was the fastest way we know till we get those fields linked to the warehouse database.

So this can be closed 🙂

tx