Why didn't PIVOT work?

Example:

SELECT prod, [newyork] AS 'newyork',[berlin] AS 'berlin',[moscov] AS 'moscov',[zurich] AS 'zurich'

FROM

(SELECT prod,point,price

FROM SalesPricesTable) p

PIVOT

(

MAX (price)

FOR point IN

( [newyork],[berlin],[moscov],[zurich] )

) AS pvt

ORDER BY prod

Another way would be...

Jeff Moden (11/14/2009)

---

Your welcome K... if you really want to learn how the Tally table works to replace WHILE Loops like Lutz did above, click on the TALLY table link...

@jeff:

Well, I didn't post it for three reasons:

1) The function I'm using is mostly copied from one of the articles I referred to several dozen times in other posts so...

Sorry to interrupt with a different subject:

It looks like the RAQ Report promoter is back (he/she must have been too busy over the last month ;-)).

If you...

SQL Server does exactly what you told to do:

update #TempTotalTable

set NoOfCol = NoOfCol

from #ColumnPerTable

where #TempTotalTable.TBName = #ColumnPerTable.TBName

This basically means to update NoOfCol with its original values, resulting in a unchanged...

Attached please find a sample code.

Some notes:

If you already have a unique value (like an ID) assigned to each row, the first CTE can be omitted.

Also, if you can guarantee,...

I think if you need somebody to work on more than (!!)1100(!!) lines of code, you should get a consultant in to help you.

If you'd like us to help you...

Would you please provide a (few) sample line(s) and your expected result?

Would make it easier for some of than talking plain theory...

Peter Brinkhaus (11/13/2009)

---

I did that test also. It requires less coding but, based on the 3 rows input, there are less rows generated within the cross apply to join with...

Is there any specific reason to make the table name colum wider than it ever can get and at the same time making the DBname smalller than it might get?

I...

It's nothing more than that. But...

Just a tiny change to Peters excellent sample:

I moved the WHERE clause from the UNION ALL part to the outer part of the query.

Performance seems to be the same (query...

Well, we're getting closer...

Following please find a proposal:

SET dateformat dmy

SELECT Emp_Id,

MAX(CASE

WHEN [Shift_Date] = '12/11/2009'

THEN ISNULL(Shift_Regular,'')

+ CASE

WHEN Shift_OnCall IS NOT NULL

THEN ' and ' +ISNULL(Shift_Regular,'')

ELSE...

Seems like you have database and/or table names that are longer than the 50 char you defined for your temp table.

As per BOL

table_name...

I wonder why you focus on not having that bookmark lookup - there might be other ways to help performance, too...

Example:

What is the reason to do a sum aggregation over...