The UNION approach is completely different from the other suggestions based on your initial requirement.

The previous suggestions are constraining that a row in the referencing table should exist in the...

Forgot to mention that you can populate the variable directly from the xml file.

...

DECLARE @x xml;

SET @x = (

SELECT * FROM OPENROWSET(

BULK 'c:\temp\xmlsamplefile.xml',

SINGLE_BLOB) AS...

You are interested in pivoting the values for all nodes positioned at specific number. If we extract the text an enumerate the rows then the numbers greater 4, 5 and...

It will be helpful if you attach / post a workable document.

In the meantime here is an example that can help you.

SET NOCOUNT ON;

USE tempdb;

GO

DECLARE @x xml = '

<Table xmlns:ss="uri">

<Row...

The option "optimize for unknown" is telling SQL Server to use same approach as when you use variables in your example. The optimizer will not sniff the value of the...

I will not argue around your needs. Check in BOL for the UNPIVOT operator or APPLY.

SET NOCOUNT ON;

USE tempdb;

GO

DECLARE @T TABLE (

c1 int NOT NULL PRIMARY KEY,

c2 varchar(25) NOT NULL

);

INSERT...

I am still waiting for your answer to see if updating a row in [TableA] and setting [Change_Value] to NULL mark is important in this context to avoid a row...

What do you expect to happen if we update TableA and set column [Charge_Value] to NULL mark for an existing row being referenced already from TableB?

If you do not want...

This article will help you to get there.

Flattening Hierarchies

http://sqlmag.com/t-sql/flattening-hierarchies

SET NOCOUNT ON;

USE tempdb;

GO

DECLARE @h TABLE (

Child char(10) NOT NULL PRIMARY KEY,

Parent char(10) NOT NULL,

[Name] varchar(35) NOT NULL

);

INSERT...

Try using a ranking function.

with C1 as (

select *, rank() over(partition by servername order by [date] DESC) as rnk

from #Time

)

select *

from C1

where rnk = 1;

Replace the "*" with the list...

This is the same problem as in your previous question and the solution is the same.

The expected result doesn't match the title of your post since you are asking for...

This problem is known as "Finding Islands". I would suggest to read this book to learn more about the solutions.

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

http://shop.oreilly.com/product/0790145323088.do

WITH...

See if this helps (having fun with strings).

The idea is to identify the breaking positions to be used by the function SUBSTRING.

DECLARE @s-2 varchar(128) = '01-08-087-0101W5';

SELECT

CAST(e1.c1 AS int)...

You can also use the sub-clause GROUPING SETS to specifiy the groups. Use function GROUPING to sort the data as desired (Total at the end).

USE tempdb;

GO

SELECT

GroupName, NumberOfCases

INTO #Temp

FROM

(

VALUES

('Grp A',10),

('Grp...

You can also use a fuction that simulates an auxiliary table of numbers.

Virtual Auxiliary Table of Numbers

http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers

SET NOCOUNT ON;

USE tempdb;

GO

-- Itzik's VATN

CREATE FUNCTION dbo.GetNums(@n AS BIGINT) RETURNS TABLE

AS

RETURN

...