Output values as single line CSV

declare @yourtable  table

([ID] int, [Date] datetime)

;

 

 

INSERT INTO @yourtable

([ID], [Date])

VALUES

(756, '2011-08-29 00:00:00'),

(756, '2011-08-31 00:00:00'),

(756, '2011-09-01 00:00:00'),

(756, '2011-09-02 00:00:00')

;

INSERT INTO @yourtable

([ID], [Date])

VALUES

(956, '2011-08-29 00:00:00'),

(956, '2011-08-31 00:00:00'),

(956, '2011-09-01 00:00:00'),

(956, '2011-09-02 00:00:00')

;

 

-- example 1-----------------------------------------------------------------

select

distinct t1.id,

STUFF((SELECT ', ' + convert(varchar(10), t2.date, 120)

   FROM @yourtable t2

   where t1.id = t2.id

   FOR XML PATH ('')), 1, 1, ''

)  AS date

from @yourtable t1;

 

 

-- query to return every table and column in order

select isc.TABLE_CATALOG
,isc.TABLE_SCHEMA
,isc.TABLE_NAME
,max(SI.rows) as RecCount
,isc.COLUMN_NAME
,isc.ORDINAL_POSITION
,isc.COLUMN_DEFAULT
,isc.IS_NULLABLE
,isc.DATA_TYPE
,isc.CHARACTER_MAXIMUM_LENGTH
,isc.NUMERIC_PRECISION
,isc.NUMERIC_SCALE
,isc.DATETIME_PRECISION
,isc.DOMAIN_NAME
from INFORMATION_SCHEMA.COLUMNS isc
join INFORMATION_SCHEMA.TABLES ist
on isc.TABLE_NAME = ist.TABLE_NAME
join SYSINDEXES SI
on ist.TABLE_NAME = OBJECT_NAME(SI.ID)
where isc.TABLE_CATALOG = 'xxxx'
and isc.TABLE_SCHEMA = 'dbo'
and ist.TABLE_TYPE = 'Base Table'
group by
isc.TABLE_CATALOG
,isc.TABLE_SCHEMA
,isc.TABLE_NAME
,isc.COLUMN_NAME
,isc.ORDINAL_POSITION
,isc.COLUMN_DEFAULT
,isc.IS_NULLABLE
,isc.DATA_TYPE
,isc.CHARACTER_MAXIMUM_LENGTH
,isc.NUMERIC_PRECISION
,isc.NUMERIC_SCALE
,isc.DATETIME_PRECISION
,isc.DOMAIN_NAME
order by TABLE_NAME, ORDINAL_POSITION

-- query to determine to find all the keys in the database, which columns are involved, and report if they are combined keys (ColumnCount)


;with CTE_SingleKey as 
(select
'Single Column Key' as KeyType
,COUNT(scu.column_name) as ColumnCount
,scu.CONSTRAINT_NAME
,kc.type
,kc.type_desc
,scu.TABLE_NAME
from sys.key_constraints kc
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE scu
on kc.name = scu.CONSTRAINT_NAME
group by
scu.CONSTRAINT_NAME
,kc.type
,kc.type_desc
,scu.TABLE_NAME
having COUNT(scu.column_name)
)
,CTE_MultiKey as 
(select
'Multi Column Key' as KeyType
,COUNT(scu.column_name) as ColumnsCount
,scu.CONSTRAINT_NAME
,kc.type
,kc.type_desc
,scu.TABLE_NAME
from sys.key_constraints kc
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE scu
on kc.name = scu.CONSTRAINT_NAME
group by
scu.CONSTRAINT_NAME
,kc.type
,kc.type_desc
,scu.TABLE_NAME
having COUNT(scu.column_name) > 1
)

select mkey.*
,scu.COLUMN_NAME
from CTE_MultiKey mkey
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE scu
on mkey.CONSTRAINT_NAME = scu.CONSTRAINT_NAME

union

select skey.*
,scu.COLUMN_NAME
from CTE_SingleKey skey
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE scu
on skey.CONSTRAINT_NAME = scu.CONSTRAINT_NAME
order by
TABLE_NAME
,CONSTRAINT_NAME
,COLUMN_NAME

-- query to find all foreign keys and referenced tables/columns

SELECT obj.name AS FK_NAME,
schem.name AS [schema_name],
Table1.name AS [table],
Columns1.name AS [column],
table2.name AS [referenced_table],
Columns2.name AS [referenced_column]
FROM sys.foreign_key_columns fkCol
INNER JOIN sys.objects obj
ON obj.object_id = fkCol.constraint_object_id
INNER JOIN sys.tables Table1
ON Table1.object_id = fkCol.parent_object_id
INNER JOIN sys.schemas schem
ON Table1.schema_id = schem.schema_id
INNER JOIN sys.columns Columns1
ON Columns1.column_id = parent_column_id AND Columns1.object_id = Table1.object_id
INNER JOIN sys.tables table2
ON table2.object_id = fkCol.referenced_object_id
INNER JOIN sys.columns Columns2
ON Columns2.column_id = referenced_column_id
AND Columns2.object_id = table2.object_id

@Rank vs @Row_Number

So recently I was asked the difference between the two and my brain froze.  There are other phrases that come to mind, but the condition of frozen brain is more PC and I'm sticking to it.

Back on topic; SQL provides a few "Ranking Functions" that can be most helpful.  There is a subtle difference between @Rank and @Row_Number I will try to demonstrate here, starting with a table called "Inventory".  For purposes of demonstration, we will assume that we can have multiple records of the same inventory item at the same location with the same quantity.  Here is the Inventory table:

The following SQL exposes the difference in the two functions:

select recordid, productid, description, location, quantity
,rank() over (partition by produtctid order by quantity desc) as rank
,row_number() over (partition by productid order by quantity desc) as row_number
from inventory
Results:

Highlighted above are the @Rank and @Row_Number for ProductID 101 at Location 1. 
Notice, that for every Product 101 at Location 1 that @Row_Number increments by 1.
@Rank, in comparison, does not. Duplicates do not cause @Rank to increment.  On rows 1 and 2, the value of @Rank is 1 - these are the number 1 values.  ODDLY though, if we look on rows 3 and 4 (also duplicate records) we see that @Rank continues with a value of 3.  This is because there are 2 records in ahead of it.

Conclusion:  @Rank and @Row_Number have different outcomes when duplicate values are present for the Partition By and Order By clauses.  

All About SQL Joins

For purposes of demonstration, lets imagine we have a zoo.
The zoo database has a table of animals it keeps named Animals.
It also has a table of the soft stuffed animals it sells in the Products table.

You have been asked to provide the following:

Find each Animal where there is not a corresponding Product:

Select A.*, P.*
from Animal A
left join Product P
on A.Animal = P.Product
where P.Product is null


Find all the Animals where there is not a corresponding Product
AND all the Products where there is not a corresponding Animal

Select A.*, P.*
from Product P
full join Animal A
on P.Product = A.Animal
where A.Animal is null or P.Product is null


Find each Animal at the zoo where there is also a corresponding Product:


select A.*, P.*
from Animal A
join Product P
on A.Animal = P.Product




Find each Animal at the zoo and also each Product whether there is a match or not:

Select A.*, P.*
from Animal A
Full join Product P
on A.Animal = P.Product

String to delimited list Function

SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO

-- =============================================
-- Author:           blowersd
-- Create date:    20120821     
-- Description:   creates a quoted and delimited list from a string for use in a sql IN statement
--                          delimeter default is comma
-- USAGE:         SELECT DBO.FN_STRINGTOLIST('Ford,Lincoln,Mercury',',')
-- EXAMPLE:   Select * from Atable A where A.columnx in dbo.fn_stringtolist(@ReportDropDownParameter)
-- =============================================
ALTER FUNCTION DBO.FN_STRINGTOLIST (
    @PARMVARIABLE VARCHAR(4000)
    ,@DELIMITER VARCHAR(1) = ','
    )
RETURNS VARCHAR(4050)
AS
BEGIN
    DECLARE @OUTLIST VARCHAR(4050);

    SET @DELIMITER = ISNULL(@DELIMITER, ',');
    SET @OUTLIST = '''' + REPLACE(@PARMVARIABLE, ',', ''',''') + '''';

    RETURN @OUTLIST;
END;