x002548's Blog - February 2005 Entries

Posts: 83
Comments: 600
Trackbacks: 40

February 2005 Entries

New twist on Delimited string per ID using many columns...AND this time into a table

OK...this is out there..based on this previous thread that I got a LOT of help on, SQL Team was asked a Question from MikeB to basically take a columns “key“ and marry a set of columns and place them all on 1 row, as a result set, or in to a table.

The correct answer is that this is a presentation issue, but I couldn't help myself. Basically take this:

fkItemID fkCostID Rate      Units
-------- -------- --------- ----------
1        21       45.0000   DAY
1        23       400.0000  LSUM
2        22       225.0000  DAY

And Produce this:

fkItemID fkCostID1 Rate1    Units1 fkCostID2 Rate2     Units2
-------- --------- -------- ------ --------- --------- ------
1        21        45.0000  DAY    23        400.0000  LSUM
2        22        225.0000 DAY    NULL      NULL      NULL

Here's the code. Just make sure to add the password for the bcp statements, or make them trusted

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE mytbEquipmentRental(fkItemID int, fkCostID int, Rate money, Units varchar(10))
GO

INSERT INTO mytbEquipmentRental(fkItemID, fkCostID, Rate, Units)
SELECT 1,        21,       45,   'DAY' UNION ALL
SELECT 1,        23,       400, 'LSUM' UNION ALL
SELECT 2,        22,       225, 'DAY'
GO

SELECT * FROM mytbEquipmentRental
GO

CREATE FUNCTION GetAllOnLine(@id int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Result VARCHAR(8000)

SELECT @Result = COALESCE(@Result + '|','') + CONVERT(varchar(15),fkCostID)+ '|' +CONVERT(varchar(15),Rate) + '|' + Units
FROM mytbEquipmentRental
WHERE fkItemID=@id

RETURN @Result
END
GO

CREATE VIEW myView99
AS
SELECT CONVERT(varchar(15),fkItemID) + '|' + dbo.GetAllOnLine(fkItemID) AS Data
FROM (SELECT DISTINCT fkItemID FROM mytbEquipmentRental) AS XXX
GO

CREATE VIEW myExport99
AS
    SELECT Data+REPLICATE('|',MAX_Delimiters - (LEN(Data)-LEN(REPLACE(DATA,'|','')))) AS ExportData
      FROM myView99
CROSS JOIN ( SELECT MAX(LEN(Data)-LEN(REPLACE(DATA,'|',''))) AS MAX_Delimiters
        FROM myView99) AS XXX
GO

SELECT * FROM myExport99
GO

DECLARE @cmd varchar(8000)
SELECT @cmd = 'bcp Northwind.dbo.myExport99 out c:\myExport99.txt -c -Usa -P'
EXECUTE master..xp_cmdshell @cmd
GO

DECLARE @MAX_Columns int, @sql varchar(8000), @x int

SELECT TOP 1 @Max_Columns = COUNT(*)
    FROM mytbEquipmentRental
GROUP BY fkItemID
ORDER BY 1 DESC

SELECT @sql = 'CREATE TABLE myTable99(fkItemID int', @x = 1
WHILE @x < = @Max_Columns
BEGIN
SELECT @sql = @sql + ', fkCostID' + CONVERT(varchar(3),@x) + ' int'
      + ', Rate'     + CONVERT(varchar(3),@x) + ' money'
      + ', Units'    + CONVERT(varchar(3),@x) + ' varchar(10)'
SELECT @x = @x + 1
END

SELECT @sql = @sql + ')'

SELECT @sql

EXEC(@sql)
GO

sp_help myTable99
GO

DECLARE @cmd varchar(8000)
SELECT @cmd = 'bcp Northwind.dbo.myTable99 in c:\myExport99.txt -c -t"|" -Usa -P'
EXECUTE master..xp_cmdshell @cmd
GO

SELECT * FROM myTable99
GO

DROP FUNCTION GetAllOnLine
DROP VIEW myExport99
DROP VIEW myView99
DROP TABLE myTable99
DROP TABLE mytbEquipmentRental
GO

posted @ Wednesday, February 23, 2005 3:30 PM | Feedback (0)

Show me The TOP n Number of things based on a key

OK, another one that seems to be often asked, and I'm sure there's a SQL Team article out there...I'm just lazy. Anyway, this post got me interested in this again (and I always have trouble trying to remember the answer), so I decided to post some solutions, so I don't have to remember. With the Help of Pat Phelan, Rudy Limeback, and Mr. B. Lindman (aka the Blind Dude), we came up with the following.

Notice the differences in the Plans. While Pat's Subquery looks cleaner, Rudy's Join seems more effecient. Any comments on the plans would be greatly appreciated.

EDIT: Sunsande points out some other shortcomings in the SQL. With their proposed SQL you not only get the top n results, but you do get ties. However n top results would require dynamic sql for this. Still I think it looks to be the best solution so far. Thanks

DECLARE @N int, @sql nvarchar(4000)
SET @N = 3 --The number of records to return for each grouping.

SET @sql =
N' SELECT a.SiteId, a.EmpId, a.Sales '
+ '    FROM myTable99 a '
+ '   WHERE a.Sales IN (SELECT DISTINCT TOP ' + CONVERT(varchar(15),@n) + ' b.Sales '
+ '         FROM myTable99 b '
+ '        WHERE a.EmpID = b.EmpId '
+ '     ORDER BY b.sales DESC) '
+ 'ORDER BY a.EmpID, a.Sales DESC, a.SiteID '

EXECUTE sp_executesql @sql

The Question here was, Show my the TOP 3 Sales for an Employee by Store

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(SiteId int, EmpId int, Sales money
, PRIMARY KEY(EmpId, Sales, SiteId))
GO

INSERT INTO myTable99(SiteId, EmpId, Sales)
SELECT 1, 1, 10.00 UNION ALL
SELECT 2, 1, 15.00 UNION ALL
SELECT 3, 1, 20.00 UNION ALL
SELECT 4, 1, 50.00 UNION ALL
SELECT 5, 1, 10.00 UNION ALL
SELECT 6, 1, 5.00 UNION ALL
SELECT 1, 2, 100.00 UNION ALL
SELECT 2, 2, 1500.00 UNION ALL
SELECT 3, 2, 2000.00 UNION ALL
SELECT 4, 2, 5000.00 UNION ALL
SELECT 5, 2, 1000.00 UNION ALL
SELECT 6, 2, 500.00 UNION ALL
SELECT 1, 3, 1.00 UNION ALL
SELECT 2, 3, 1.50 UNION ALL
SELECT 3, 3, 2.00 UNION ALL
SELECT 4, 3, 5.00 UNION ALL
SELECT 5, 3, 1.00 UNION ALL
SELECT 6, 3, .50
GO

SET SHOWPLAN_TEXT ON
GO

DECLARE @N int
SET @N = 3 --The number of records to return for each grouping.

SELECT a.SiteId, a.EmpId, a.Sales
   FROM myTable99 a
   WHERE (SELECT Count(*)
      FROM myTable99 b
      WHERE b.EmpId   = a.EmpId
         AND a.Sales <= b.Sales) <= @N
   ORDER BY a.EmpID, a.Sales DESC, a.SiteID

    SELECT a.EmpId, a.SiteId, a.sales
      FROM myTable99 a
INNER JOIN myTable99 b
        ON a.EmpId = b.EmpId
       AND a.Sales <= b.Sales
GROUP BY a.EmpId, a.SiteId, a.sales
HAVING COUNT(*) <= @N
ORDER BY a.EmpId, a.sales desc
GO

SET SHOWPLAN_TEXT OFF
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

posted @ Thursday, February 10, 2005 10:02 AM | Feedback (3)

How do I search a database for the umpteenth time

OK, so I got tired of writing this...so I decided to build a bells and whistles full database search. Now at first blush, the reaction that this is bad....and it is...but I've been handed so many poorly designed databases, I felt for this persons post. So now I'm done...I don't have to write it again....I guess we could expanded it to server the entire server....

EDIT: Modified to Search Text and ntext as well. Changes are highlighted in Red. Anyone know why the Row counts are still being produced?

USE Northwind
GO

SET NOCOUNT OFF
CREATE TABLE myTableSearch99(
SPID int
, SearchDate datetime
, TABLE_CATALOG sysname
, TABLE_SCHEMA sysname
, TABLE_NAME sysname
, COLUMN_NAME varchar(255)
, DATA_TYPE varchar(25)
, SEARCHARG varchar(2000)
, RESULTS varchar(4000))
GO

CREATE PROC myDBSearch99
@SearchArg varchar(2000)
AS
BEGIN
SET NOCOUNT ON
DECLARE @sql varchar(8000), @Date datetime
DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAME sysname, @DATA_TYPE varchar(25)
SELECT @date = GetDate()

DELETE FROM myTableSearch99 WHERE SPID = @@SPID

DECLARE myCursor99 CURSOR FOR
  SELECT c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME , c.DATA_TYPE
    FROM INFORMATION_SCHEMA.Columns c
     INNER JOIN INFORMATION_SCHEMA.Tables t
      ON c.TABLE_CATALOG = t.TABLE_CATALOG
     AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
     AND c. TABLE_NAME   = t.TABLE_NAME
   WHERE t.TABLE_TYPE = 'BASE TABLE'
     AND c.DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext','datetime')
     AND t.TABLE_NAME <> 'myTableSearch99'

OPEN myCursor99
FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE

INSERT INTO myTableSearch99(SPID,SearchDate,TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME, SEARCHARG)
SELECT @@SPID, CONVERT(varchar(25),@Date,120), @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @SearchArg

WHILE @@FETCH_STATUS = 0
   BEGIN
  SELECT @sql = 'SET NOCOUNT ON ' + CHAR(13)
    +' INSERT INTO myTableSearch99(SPID,SearchDate,TABLE_CATALOG,TABLE_SCHEMA, ' +
    + 'TABLE_NAME,COLUMN_NAME,DATA_TYPE,SEARCHARG,RESULTS) '
    + 'SELECT ' + CONVERT(varchar(15),@@SPID) + ',' + ''''
    + CONVERT(varchar(25),@Date,120) + '''' + ',' + '''' +
    + @TABLE_CATALOG + ''',''' + @TABLE_SCHEMA + ''',''' + @TABLE_NAME + ''',' + ''''
    + @COLUMN_NAME + ''',' + '''' + @DATA_TYPE + ''',' + ''''+ @SearchArg + '''' + ','
    + 'CONVERT(varchar(4000),'+@COLUMN_NAME+')'
    + ' FROM ' + '['+ @TABLE_CATALOG + '].[' + @TABLE_SCHEMA + '].[' + @TABLE_NAME + '] '
    + ' WHERE PATINDEX( ' + '''' + '%' + @SearchArg + '%' + '''' + ','
    + CASE WHEN @DATA_TYPE = 'datetime' THEN 'CONVERT(varchar(25),'+@COLUMN_NAME
    + ',121)' ELSE @COLUMN_NAME END
    + ') <> 0'
--    + ' WHERE ' + @COLUMN_NAME + ' LIKE ''%' + @SearchArg + '%'''
--  SELECT @sql
  EXEC(@sql)
  FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE
   END

SET NOCOUNT OFF

SELECT
    SPID
  , SearchDate
  , TABLE_CATALOG
  , TABLE_SCHEMA
  , TABLE_NAME
  , COLUMN_NAME
  , DATA_TYPE
  , SEARCHARG
  , RESULTS
   FROM    myTableSearch99 o
WHERE SPID = @@SPID
    AND SearchDate = ( SELECT MAX(SearchDate)
    FROM myTableSearch99 i
          WHERE i.SPID = o.SPID)
CLOSE myCursor99
DEALLOCATE myCursor99
END
GO

EXEC myDBSearch99 'Ale'
GO

EXEC myDBSearch99 'Stout'
GO

EXEC myDBSearch99 '1996-07-04'
GO

SELECT * FROM myTableSearch99 ORDER BY SearchDate DESC, COLUMN_NAME
GO

This was the original

SET NOCOUNT OFF
TRUNCATE TABLE myTableSearch99
DROP TABLE myTableSearch99
DROP PROC myDBSearch99

USE Northwind
GO

SET NOCOUNT OFF
CREATE TABLE myTableSearch99(
SPID int
, SearchDate datetime
, TABLE_CATALOG sysname
, TABLE_SCHEMA sysname
, TABLE_NAME sysname
, COLUMN_NAME varchar(255)
, SEARCHARG varchar(2000)
, RESULTS varchar(4000))
GO

DECLARE myCursor99 CURSOR FOR
  SELECT c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME
    FROM INFORMATION_SCHEMA.Columns c
     INNER JOIN INFORMATION_SCHEMA.Tables t
      ON c.TABLE_CATALOG = t.TABLE_CATALOG
     AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
     AND c. TABLE_NAME   = t.TABLE_NAME
   WHERE t.TABLE_TYPE = 'BASE TABLE'
     AND c.DATA_TYPE IN ('char','nchar','varchar','nvarchar','datetime')

OPEN myCursor99
FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME

INSERT INTO myTableSearch99(SPID,SearchDate,TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME, SEARCHARG)
SELECT @@SPID, CONVERT(varchar(25),@Date,120), @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @SearchArg

WHILE @@FETCH_STATUS = 0
   BEGIN
  SELECT @sql = 'INSERT INTO myTableSearch99(SPID,SearchDate,TABLE_CATALOG,TABLE_SCHEMA, ' +
    + 'TABLE_NAME,COLUMN_NAME,SEARCHARG,RESULTS) '
    + 'SELECT ' + CONVERT(varchar(15),@@SPID) + ',' + ''''
    + CONVERT(varchar(25),@Date,120) + '''' + ',' + '''' +
    + @TABLE_CATALOG + ''',''' + @TABLE_SCHEMA + ''',''' + @TABLE_NAME + ''',' + ''''
    + @COLUMN_NAME + ''',' + ''''+ @SearchArg + '''' + ',' + @COLUMN_NAME
    + ' FROM ' + '['+ @TABLE_CATALOG + '].[' + @TABLE_SCHEMA + '].[' + @TABLE_NAME + '] '
    + ' WHERE ' + @COLUMN_NAME + ' LIKE ''%' + @SearchArg + '%'''
--  SELECT @sql
  EXEC(@sql)
  FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME
   END

SET NOCOUNT OFF

SELECT
    SPID
  , SearchDate
  , TABLE_CATALOG
  , TABLE_SCHEMA
  , TABLE_NAME
  , COLUMN_NAME
  , SEARCHARG
  , RESULTS
   FROM    myTableSearch99 o
WHERE SPID = @@SPID
    AND SearchDate = ( SELECT MAX(SearchDate)
    FROM myTableSearch99 i
          WHERE i.SPID = o.SPID)
CLOSE myCursor99
DEALLOCATE myCursor99
END
GO

EXEC myDBSearch99 'Ale'
GO

EXEC myDBSearch99 'Stout'
GO

SELECT * FROM myTableSearch99 ORDER BY SearchDate DESC, COLUMN_NAME
GO

SET NOCOUNT OFF
TRUNCATE TABLE myTableSearch99
DROP TABLE myTableSearch99
DROP PROC myDBSearch99

posted @ Tuesday, February 08, 2005 1:20 PM | Feedback (4)

Mimic Oracles INSTR Function

Starting at a user defined location in a string to find the nth occurance of a target string's starting location

EDIT: OK, I got Jay's set based method to work. And I mistook Jay for Jay White (Page47) but I was wrong (but I should be given a pass, because it's just like what Mr. 47 would have written). ~~Now we have to ask, Jay who?~~

Not anymore, thanks Jay

Anyway Thanks Jay. Just as a note: This requires a numbers table. The concept of using this to eliminate recursion and cursors is sort of akin to turning a light on after stumbling around in the dark for so long (and even stumbling around in the light, since I couldn't employee this concept in my original shot at this). The reasons to not use set based processing is becoming smaller and smaller on a daily basis. Anyway, enjoy.

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE numbers(n int)
GO

DECLARE @x int
SET @x = 1

WHILE @x < 1001
BEGIN
INSERT INTO numbers(n) SELECT @x
SELECT @x = @x + 1
END
GO

CREATE FUNCTION udf_Instr
(@str1 varchar(8000), @str2 varchar(8000), @start int, @Occurs int)
RETURNS int
AS
BEGIN
DECLARE @instr int
SELECT TOP 1 @instr = instr
FROM (
SELECT   d.*
  , (SELECT COUNT(1)
       FROM (SELECT DISTINCT CHARINDEX(@str1,SUBSTRING(@str2,@start,LEN(@str2)-@start+1),n) AS instr
               FROM numbers
              WHERE n <= LEN(@str2))AS d2
      WHERE d2.instr <= d.instr) AS ct

FROM (SELECT DISTINCT CHARINDEX(@str1,SUBSTRING(@str2,@start,LEN(@str2)-@start+1),n) AS instr
FROM numbers
WHERE n <= LEN(@str2)) AS d
) AS d
GROUP BY instr,ct
HAVING ct <= @occurs
ORDER BY ct DESC
RETURN @instr
END

SELECT dbo.udf_Instr('x','axbxcxdx',1,4)
GO

My Original shot:

CREATE FUNCTION udf_Instr
(@str1 varchar(8000), @str2 varchar(8000), @start int, @Occurs int)
RETURNS int
AS
BEGIN
DECLARE @Found int, @LastPosition int
SET @Found = 0
SET @LastPosition = @start - 1

WHILE (@Found < @Occurs)
BEGIN
  IF (CHARINDEX(@str1, @str2, @LastPosition + 1) = 0)
   BREAK
    ELSE
   BEGIN
    SET @LastPosition = CHARINDEX(@str1, @str2, @LastPosition + 1)
    SET @Found = @Found + 1
   END
END

RETURN @LastPosition
END
GO

SELECT dbo.udf_Instr('x','axbxcxdx',1,4)
GO

DROP FUNCTION udf_Instr
GO

posted @ Wednesday, February 02, 2005 1:03 PM | Feedback (11)

Article Categories

Archives

Post Categories

Image Galleries

Misc

SQL Server