Archive for the ‘Sql’ Category

Quick concatenation of rows in T-SQL using FOR XML PATH

February 9th, 2017

If you want to quickly concatenate a resultset, here's a quick and easy way to do it.

SELECT 
	ProductType AS [data()]
FROM
	dbo.Product
FOR XML PATH ('')

SELECT 
	ProductType AS [text()]
FROM
	dbo.Product
FOR XML PATH ('')

The example with data() will yield a space-delimited list of values. The example with text will yield a continuous list of values. Here's how to supply a delimiter.

SELECT 
	',' + ProductType AS [data()]
FROM
	dbo.Product
FOR XML PATH ('')

You can also use DISTINCT to have recurring values appear only once.

SELECT 
	DISTINCT 
	',' + ProductType AS [data()]
FROM
	dbo.Product
FOR XML PATH ('')

You can also concatenate multiple fields. The example below also shows how to remove the leading delimiter.

SELECT
	STUFF(
		(SELECT 
			DISTINCT 
			';' + ProductType + ': ' + ProductTypeCategory AS [data()]
		FROM
			dbo.Product
		FOR XML PATH ('')), 1, 1, '')

Enjoy!

Posted in Sql | Comments (0)

SQL Server Management Studio shows result tab that does not match the query

April 22nd, 2013

I couldn't figure out why my SQL Server Management Studio (SMSS) would show results that would not match the query underneath. They all looked like results for sp_who and sp_lock, system stored procedures that can be used for fetching information from SQL Server.

It turns out that, by default, ctrl-1 and ctrl-2 are assigned to run sp_who and sp_lock. Since the shortcuts for switching between PC's on my KVM switch are set to ctr-alt-1 and ctrl-alt-2, I would regularly get this issue without really understanding what was going on.

If you want to get rid of this, simply go to Tools, Options, Keyboard and clear the text for the keys that you accidentally press once in a while.

Posted in Sql | Comments (0)

T-Sql: How to find the maximum value for a field, or the second largest value, or the third largest value, or …

January 20th, 2012

Many queries you write will be about finding the most recent data for a certain type of event, or the highest value for a certain object. While this is a straightforward query type, it gets complicated and ugly when you have to link two or more tables to find your maximum or minimum value. And it gets even more complicated when you don’t have to find the maximum value but the value that is just below the maximum value.

In the rest of the article, I will be talking about the maximum value for a field, but it’s also true of course for the minimum value.
The query type you will encounter most often contains a subquery where the current value is compared to the maximum value for that particular field. A simple example is presented below.

  1.  
  2. SELECT
  3. MAX(tb2.Field) AS MaxValueForField
  4. FROM
  5. table1 tb1
  6. INNER join
  7. table2 tb2
  8. ON tb2.foreign_key = tb1.primary_key
  9. WHERE
  10. tb2.Field = (
  11. SELECT
  12. MAX(_tb2.field)
  13. FROM
  14. table1 _tb1
  15. INNER join
  16. table2 _tb2
  17. ON _tb2.foreign_key = _tb1.primary_key
  18. WHERE
  19. _tb1.primary_key = tb1.primary_key
  20. )
  21. GROUP BY
  22. tb1.primary_key
  23.  

The subquery can be avoided with a Common Table Expression that not only speeds up the entire query but also offers a solution for another problem that I will be discussing below.

  1.  
  2. ;
  3. WITH tb2Sorted
  4.  
  5. AS
  6.  
  7. (
  8. SELECT
  9. Row_Number() OVER (partition BY tb1.primary_key ORDER BY tb2.Field DESC) AS RowId,
  10. Field
  11. FROM
  12. table1 tb1
  13. INNER join
  14. table2 tb2
  15. ON tb2.foreign_key = tb1.primary_key
  16. )
  17. SELECT
  18. Field AS MaximaleIngangsDatum
  19. FROM
  20. tb2Sorted
  21. WHERE
  22. RowId = 1
  23.  

This query performs better than the first one but also offers another advantage.

You can easily find the second or third largest value by simple changing the RowId. Bear in mind that this will not remove duplicates. If the values for Field are 1,1,2,5,5 then RowId 1 will yield 5 and RowId 2 will also yield 5. You can find the top 5 largest values for a certain field by simply changing the where clause to read RowId < 6.

  1.  
  2. ;
  3. WITH tb2Sorted
  4.  
  5. AS
  6.  
  7. (
  8. SELECT
  9. Row_Number() OVER (partition BY tb1.primary_key ORDER BY tb2.Field DESC) AS RowId,
  10. Field,
  11. RowId
  12. FROM
  13. table1 tb1
  14. INNER join
  15. table2 tb2
  16. ON tb2.foreign_key = tb1.primary_key
  17. )
  18. SELECT
  19. Field AS MaximaleIngangsDatum
  20. FROM
  21. tb2Sorted
  22. WHERE
  23. RowId < 6
  24.  

Posted in Sql | Comments (0)

T-SQL: Find any string in any column in any table in a database.

January 19th, 2012

Have you ever found yourself in need of finding a certain string in an entire database? As a freelance BI consultant, this happens to me rather frequently when analysing where and how data is stored and how to retrieve it.

So I came up with this code. It lets you find a string in any column in any table on your database. It's a pretty length search if you have a lot of data and a lot of columns and tables, but it sure beats searching by hand.

Be warned! This may run a pretty long time on large databases.

  1.  
  2. DECLARE @searchString NVARCHAR(MAX)
  3. DECLARE @sqlString NVARCHAR(MAX)
  4. DECLARE @processedString NVARCHAR(MAX)
  5.  
  6. -- Insert your searchstring below
  7. -- Use \ as escape character
  8. -- examples:
  9. -- to search for 'I'm waiting', use 'I\'m waiting'
  10. -- to search for '25%' use '25\%'
  11.  
  12. SELECT
  13. @searchString = 'naar achter'
  14.  
  15. DECLARE @resultTable TABLE
  16. (
  17. table_name NVARCHAR(255),
  18. COL_NAME NVARCHAR(255),
  19. value_found NVARCHAR(MAX),
  20. OBJECT_ID INT,
  21. col_id INT
  22. )
  23.  
  24. SELECT
  25. @sqlString = '-- start',
  26. @processedString = ''
  27.  
  28. WHILE
  29. @sqlString > ''
  30. BEGIN
  31.  
  32. SELECT
  33. @sqlString = '',
  34. @processedString = ''
  35.  
  36. SELECT
  37. @processedString = @processedString +
  38. CASE
  39. WHEN DATALENGTH(@sqlString) < 7500 -- assume a sql statement will never go beyond 500 characters
  40. THEN
  41. '
  42. select
  43. ''' + QUOTENAME(SCHEMA_NAME(tbl.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(tbl.OBJECT_ID)) + ''' as table_name,
  44. ''' + col.name + ''',
  45. NULL as value_found,
  46. ' + CONVERT(NVARCHAR,tbl.OBJECT_ID) + ' as object_id,
  47. ' + CONVERT(NVARCHAR,col.column_id) + ' as col_id
  48. '
  49. ELSE
  50. ''
  51. END,
  52. @sqlString = @sqlString +
  53. CASE
  54. WHEN DATALENGTH(@sqlString) < 7500 -- assume a sql statement will never go beyond 500 characters
  55. THEN
  56. '
  57. select
  58. top 1
  59. ''' + QUOTENAME(SCHEMA_NAME(tbl.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(tbl.OBJECT_ID)) + ''' as table_name,
  60. ''' + col.name + ''',
  61. ' + col.name + ' as value_found,
  62. ' + CONVERT(NVARCHAR,tbl.OBJECT_ID) + ' as object_id,
  63. ' + CONVERT(NVARCHAR,col.column_id) + ' as col_id
  64. from
  65. ' + QUOTENAME(SCHEMA_NAME(tbl.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(tbl.OBJECT_ID)) + '
  66. where
  67. ' + col.name + ' like ''%' + @searchString + '%'' escape ''\''
  68. '
  69. else
  70. ''
  71. end
  72. from
  73. sys.columns col
  74. inner join
  75. sys.tables tbl
  76. on col.object_id = tbl.object_id
  77. and col.user_type_id in (231, 167, 175, 239)
  78. -- nvarchar, varchar, char, nchar
  79. -- no text
  80. left join
  81. @resultTable rst
  82. on col.column_id = rst.col_id
  83. and col.object_id = rst.object_id
  84. where
  85. rst.table_name is null
  86.  
  87. print @sqlString
  88. print @processedString
  89.  
  90. insert into
  91. @resultTable
  92. exec
  93. (@sqlString)
  94.  
  95. insert into
  96. @resultTable
  97. exec
  98. (@processedString)
  99.  
  100. print '---8<-----------------------------------------------------------------------------------------------'
  101.  
  102. END
  103.  
  104. SELECT
  105. table_name,
  106. COL_NAME,
  107. value_found
  108. FROM
  109. @resultTable
  110. WHERE
  111. value_found IS not null
  112. ORDER BY
  113. 1, 2, 3
  114.  

Posted in Hacks, Sql | Comments (0)

Drop all foreign keys on a database and optionally drop all tables

January 16th, 2012

If you've ever found the need to drop or remove all foreign keys on a database then here's a little script that runs without cursors.

  1.  
  2. DECLARE @SQL NVARCHAR(MAX)
  3.  
  4. SELECT
  5. @SQL = '-- Start '
  6.  
  7. WHILE @SQL > ''
  8. BEGIN
  9. SELECT
  10. @SQL = ''
  11.  
  12. SELECT
  13. @SQL = @SQL +
  14. CASE
  15. WHEN DATALENGTH(@SQL) < 7500 THEN
  16. N'alter table ' + QUOTENAME(schema_name(schema_id))
  17. + N'.'
  18. + QUOTENAME(OBJECT_NAME(parent_object_id))
  19. + N' drop constraint '
  20. + name
  21. + CHAR(13) + CHAR(10) --+ 'GO' + CHAR(13) + CHAR(10)
  22. ELSE
  23. ''
  24. END
  25. FROM
  26. sys.foreign_keys
  27.  
  28. PRINT @SQL
  29. EXEC SP_EXECUTESQL @SQL
  30. PRINT '---8<------------------------------------------------------------------------------------------'
  31. END
  32.  
  33. GO
  34.  
  35. ---8<-------------------------------------------------------------
  36. -- Uncomment the line below to drop all tables too
  37.  
  38. --exec sp_msforeachtable 'drop table ?'
  39.  
  40. PRINT 'All done'
  41.  

Posted in Hacks, Sql | Comments (1)

SEO Powered by Platinum SEO from Techblissonline