You could use a split string fuction (search for "DelimitedSplit8k" on this site for a T-SQL solution).

You could add char(10) +char(13) (CR/LF) after each CASE part.

However, SSMS will not display it correctly in grid mode, only in text mode. I'm not sure what tool you'll...

Did you try to google for "vehicle maintainance database"?

I'm sure there'll be some results giving you some ideas.

The db diagram question is simply to vague to answer.... Please provide more...

SeanLange (9/24/2010)

---

You can't just stick a few characters together and cast them as a date, there has to be some sort of delimiter (typically a '/'). If possible maybe...

Insteadof PIVOT I'd rather use the CrossTab concept as described in the related link in my signature.

Once the static CrossTab works as required you might want to have a look...

Be careful when using ISNUMERIC...

Here's an example for using ISNUMERIC vs. REGEX:

DECLARE @tbl TABLE

(

val VARCHAR(10)

)

INSERT INTO @tbl

SELECT '1e0' UNION ALL

SELECT '1w0' UNION ALL

SELECT '1d0' UNION ALL

SELECT...

I'd rather use XQuery instead of OpenXML since it provides more flexibility when dealing with xml data.

DECLARE @stdXML XML

SET @stdXML =

'<Standards>

<Standard StandardID="8" StandardRatingID="1"></Standard>

<Standard StandardID="13" StandardRatingID="26"></Standard>

</Standards>'

DECLARE @TmpStds TABLE

(

StdID INT,

StdRatingID INT

)

INSERT...

The actual migration is most probably not as complex as the discussion before the migration.

We're facing a similar situation right now: the (outsourced) server folks trying to force us into...

The problem is you're using a column alias that is unknown to the ROW_NUMBER() function the way the query is written.

Two options:

a) replace the Score alias with the original case...

Max-498379 (9/22/2010)

---

Thanks for your replies.

If you look carefully, you will notice that row_number() or rank() will not work in this case. A recursion may work, but I just used a...

Sounds like a homework question...

Did you consider using ROW_NUMBER()?

You can use this function to number rows in a specific order (ORDER BY) and, if needed, restart numbering based on values...

I'm not sure what you're struggling with...

After changing your sample code pointing to a file that exists in my system your code did run just fine. I had one row...

Please provide your expected results based on your sample data.

After changing some column names and the calculation inside the user_viewing CTE I got the following results:

Billing Month(No column name)Users

Dec, 20090hrs...

@Angelindiego: Glad we could help!! 😀

@luke: 😛 😉

The CTE stuff came to my mind based on Angelindiegos original post. I usually don't use it either... but it definitely makes troubleshooting...

pierre.daubresse (9/20/2010)

---

Hi,

I looked at "Cross Tabs and Pivots, Part 1 – Converting Rows to Columns" and it looks like The Multi-Aggregate Cross Tab is the solution for me. But I...