|
|
This weeks tip:
Recently I needed to convert a column to a file of comma separated values (CSV). There are a few ways to do this via TSQL, here’s a simple one – use COALESCE. The idea is to declare a variable, set it equal to itself and a comma and the next data value, finally select the variable. See the example as it’s much easier to follow.
The Setup:
Create table CSVTest (Name varchar(50))
GO
Insert CSVTest Values('Andy Lohn'),
('Adrian Lohn'),
('Maggie Lohn'),
('Nala Lohn')
Go
Select Name from CSVTest
Go
This returns, as expected the following:
The Example:
Declare @Names varchar(2000)
Select @Names = Coalesce(@Names + ', ','') + Name from CSVTest
Select @Names as 'CSV List'
This returns the same results, except in as a comma separated list instead of a column.
Here are some related posts from the intraweb – including other ways to do this with user defined functions and XML:
Previous Tips O’ the Week
Last Tuesday was the July 2011 PASSMN Meeting – Ask the Experts. I got this great idea during the meeting to write up my notes with all the links to the web sites referenced. I took good notes and that night I got home and after my family went to bed I was going to start typing them up. Then I saw this:
Dan English (Blog/Twitter) beat me to the punch, and not only did he beat me to the punch, he did a much better job than I would have. Thank you Dan!
Here are his links and notes:
This weeks tip:
The World is Flat – I just started reading that book this weekend, I know I’m a bit behind the times, but it’s still interesting. If you don’t know the book – I would suggest reading it as it contains a lot of topics on outsourcing and specialized work – such as SQL development, hosting, etc. This hits real close to home for me these days.
What does this have to do with the TSQL Tip o’ the Week? Well if you are working on systems that are used in different time zones, consider using GETUTCDATE as the standard for recording the date/time, that way irreguardlessly regardless of what time zone you are in you will always be comparing apples to apples.
Here is a good post from the intraweb showing an example of where this is very helpful:
Previous Tips O’ the Week
This weeks tip:
I use wildcards and the “LIKE” operator in my TSQL all the time – a lot of the time to find table names, column names etc.
Select OBJECT_NAME (OBJECT_ID),
NAME
from sys.columns
where name like '%file%'
Ninety eight percent of the time, I use the %. The other two percent, I need to look up the exact syntax. Well, here’s a quick reference for you:
| Wild Card |
Description |
| % |
Any string of characters i.e. %ID% |
| _ |
Any single character i.e. _ndy |
| [] |
Any single single character in a range specified or set i.e. [A-L] OR [ABC] |
| [^] |
Any single single character not in a range specified or set i.e. [^A-L] OR [^ABC] |
Sometimes an issue comes up where you need to search for the actual wildcard character, for more info on that, check out the section titled “Using Wildcard Characters As Literals” from the MSD article on LIKE.
Here are some related posts from the intraweb:
Previous Tips O’ the Week
Here’s your chance to have your burning questions answered….
Sponsor: Superior Consulting
Date: July 19th, 2011
Time: 3:00 PM – 5:00 PM
Location: 8300 Norman Center Drive, 9th Floor, Bloomington, MN 55437
Click here to RSVP
Live Meeting:
Agenda:
- 3:00-3:15 : Registration / hospitality / networking
- 3:15-3:30 : Announcements etc.
- 3:30-4:45 : Ask the Experts
- 4:45-5:00 : Closing comments/prize giveaways
Ask the Experts Panel:
- Lara Rubbelke, Microsoft (Blog/Twitter)
- Dan English, Superior Consulting Services (Blog/Twitter)
- Mark Vaillancourt, Digineer (Blog/Twitter)
- Bill Preachuk, Emergent Networks (Blog)
- Zach Mattson, Patterson Companies (Twitter)
This years discussion will be moderated by Whoopi Goldberg Jason Strate (Blog/Twitter).
This weeks tip:
Again….What’s the fastest land animal? The Cheetah.
What’s the fastest way to test a query? Don’t use your mouse and learn lots of keyboard shortcuts!
Some of my favorites in the query editor of SQL Server Management Studio are:
- All the text selection short cuts – standard in most Windows applications
- F5 – Execute the highlighted query
- CTRL + T – Results to Text
- CTRL + D – Results to Grid
- CTRL + R – Show or hide the results pane
- CTRL + Shift + R – Refresh the Intellisense cache
- CTRL+K then CTRL+C – Comment out the highlighted text
- CTRL+K then CTRL+U – Uncomment the highlighted text
Pick one or two of these for a day and work on them. In no time you will be writing, testing and understanding more code than ever before – and if your mouse runs out of batteries – it’s just not a problem.
Another good way to embed these in your mind is to hang a cheat sheet in your office for a quick and easy reference.
Here are some related posts from the intraweb:
Previous Tips O’ the Week
|
|
