Tuesday, July 30, 2013

[SQL Server] Need assistance doing a PIVOT-like transformation on VARCHAR data.

[SQL Server] Need assistance doing a PIVOT-like transformation on VARCHAR data.

Need assistance doing a PIVOT-like transformation on VARCHAR data.

Posted: 30 Jul 2013 02:40 PM PDT

I am trying to write a query using the table below - this table comes from a vendor supplied system, so I can't modify it:[code="plain"][b]Item_ID, Tag[/b]ITEM1, BlueITEM1, WarmITEM2, GreenITEM3, CoarseITEM2, Fine[/code]There is a maximum of four Tag records for one Item. I want to get the result set below:[code="plain"][b]Item_ID, TAG1, TAG2, TAG3, TAG4[/b]ITEM1, Blue, Warm, NULL, NULLITEM2, Green, Fine, NULL, NULLITEM3, Coarse, NULL, NULL[/code]I have done this previously by creating a temp table with an ID column and the structure of the second table, inserting the select distinct Item_IDs, then using a while loop to iterate through the first table, updating the rows in the second where the second table's Item_ID matches the first's, but there isn't an existing Tag field with the value for that Item_ID.The problem with this solution is it means looping through the first table, then inside of that loop, looping through the second and updating where needed, which is very resource intensive. I've looked at the PIVOT command, but I can't find any samples with varchar values (the samples I've seen all have some sort of aggregation/count which I can't see how to adapt).Does anyone know any more efficient ways of doing the above transformation?

SQL Server 2005 and SQL Server 2012

Posted: 30 Jul 2013 09:56 AM PDT

About start Project with upgrade to IPv6. Need advice on how SQL Server is affected to the 2005 and 2005 if there is a difference. What need to be tested? and or changed? Know the question is vague, but any help will be appreciated. Thks in advance.

SQL Server 2005 system databases

Posted: 30 Jul 2013 09:50 AM PDT

SQL Server 2005;Was wondering if I can point the system databases to another set of system database files. Such as I have a set start up is pointing to on D:\ and I want them to point to a set on F:\. Is this possible and how or what steps should I take. If get them pointing to the F:\ I can delete the files on D:\. Any other info needed let me know.

need help with applying function within Query

Posted: 30 Jul 2013 12:48 AM PDT

Hi allIts my first post here! Glad to be here.Hopefully someone can help me with the following.If you look at the attach screenshot, you will see the 3 SQL table i am working with along with the query i will need to run on these tables. The problem is that in the "CuRRENTS" table, the first column is in "Ticks" format instead of DATETIME! so in my query i need to get the values from that column (Timestamp_ID) to DATETIME format. Now by looking at theat SQL Database, i saw 2 fucntions (see attached notepads) that may have been created to do exactly that: converting that column into DATETIME. The problem is that i dont know how i would use them into my query.Is there anyone that could help?thanks in advance
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog