Hi,

At the part of eliminate duplicate what if we have a '!' character in string?

So following solution is so simple and suitable for any scenario.

And I use a new way...

SELECT col0, SUM(col2) AS sum_col2, MAX(col3) AS max_col3

FROM your_table_name_here

GROUP BY col0

At last, I got it.

CREATE FUNCTION Cross_tabbing (@product_name VARCHAR(15), @year INT)

RETURNS TABLE AS

RETURN

(SELECT SUM(qty * product_price) AS total

FROM Sales S

WHERE S.product_name = @product_name

...

You are welcome:-)

INSERT INTO #insert (Txt)

SELECT Txt

FROM #Temp T

CROSS JOIN

(SELECT TOP 100 ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM sys.columns)D(n)

WHERE D.n <= T.Cnt

You are welcome.

Try this also:

SELECT D.Office, ISNULL(D1.d, 0) AS Desktop

,ISNULL(D2.l, 0) AS Laptop

,ISNULL(D3.p, 0) AS Printer

FROM (SELECT DISTINCT Office

FROM table_name) D

OUTER...

Also

SELECT D.Office,

(SELECT COUNT(*)

FROM table_name

WHERE asset = 'desktop'

AND Office = D.Office) AS 'Desktop'

,(SELECT COUNT(*)

FROM table_name

WHERE asset = 'laptop'

AND Office = D.Office)...

Also

;WITH Descktop AS

(

SELECT Office, Count(Asset) AS Desktop

FROM stg_AssetSummary

WHERE Asset= 'Desktop'

Group By Office

), Laptop AS

(

SELECT Count(Asset) AS Laptops

FROM stg_AssetSummary

WHERE Asset= 'Laptop'

Group By Office

), Printer AS

(

SELECT Count(Asset) AS Printers

FROM stg_AssetSummary

WHERE Asset= 'Printer'

Group...

SELECT office,

SUM(CASE WHEN asset = 'desktop' THEN 1 ELSE 0 END) AS [Desktop],

SUM(CASE WHEN asset = 'laptops' THEN 1 ELSE 0 END) AS [Laptops],

SUM(CASE WHEN asset = 'printers' THEN 1...

Crossposting!

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=143491