SQL Server – Multiple data rows into one generated column, XML Path char(13), and removing that last carriage control in SSRS Expression

Let’s say you have a table called ‘car_make’ and another table called ‘car_model’. There is a one to many relationship between make and model.

You want to bring back a result set of all makes, 1 row per make, but you want only 1 generated column for model that has all the models concatenate into that 1 column.

You also want to add carriage controls so each model will appear on a separate line in your row on your report.

Let’s say you have 4 models for your 1 make of Chevy. Here is the output of what you are striving for:

Make     Model
--------------
Chevy    Astro
         Beretta
         Blazer
         Caprice

The following SQL will give you this result:

SELECT make,
left(ModelsConcat.Models,LEN(ModelsConcat.Models)-PATINDEX('%[^'+CHAR(13)+CHAR(10)+']%',REVERSE(ModelsConcat.Models))+1) As CarModels
OUTER APPLY (SELECT DISTINCT SUBSTRING((SELECT DISTINCT (m1.model + ', ' + + char(13))
 FROM car_make AS m1
 WHERE a.make = m1.make
 ORDER BY (m1.model + ', ' + char(13)) For Xml Path(''), type).value('.', 'nvarchar(max)') ,1,2000) AS Models 
 FROM car_model) AS ModelsConcat 
 FROM car_make a

In your expression, you simply reference the field like this:

=Fields!CarModels.Value

 

 

SQL Server – Case-Sensitive search in SQL

If your sql server is installed with the default collation of case-insensitive, you can accomplish this in sql instead.

You can determine if your installation collation is case-insensitive by running this query:

SELECT SERVERPROPERTY ('Collation')
GO

If the result is Collate SQL_Latin1_General_CP1_CI_AS, then it it is case-insensitive.

For example, I may have data stored in column as AbC and ABC. I want only rows where the column = ‘AbC’

You can accomplish this by using the Collate method in sql to tell your installation that you want to override the default property with the case-sensitive property like this (SQL_Latin1_General_CP1_CS_AS):

SELECT *
 FROM dbo.some_table
 WHERE some_value = 'AbC' Collate SQL_Latin1_General_CP1_CS_AS

SQL Server – Turning a Comma Separated string into individual rows

Let’s say you have a column in a table that is comma delimited. The value in the column is  ‘APPLE, ORANGE, GRAPE’. You want to break down this string value into individual rows. You may also want to create an auto-incremented IDENTITY column too. Here is one way it can be done.

DECLARE @TempTable TABLE(seq_no int IDENTITY(1,1) PRIMARY KEY, comma_del_string varchar(MAX))
INSERT INTO @TempTable
SELECT 'APPLE, ORANGE, GRAPE'
select * from @TempTable

DECLARE @S varchar(max)
DECLARE @Split char(1) = ','
DECLARE @X xml

SELECT @S = (select comma_del_string from @TempTable where SEQ_NO = 1)
SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,@Split,'</s><s>') + '</s></root>')

DECLARE @userData TABLE(seq_no int IDENTITY(1,1) PRIMARY KEY, answer varchar(MAX))
INSERT INTO @userData
SELECT [Value] = T.c.value('.','varchar(20)') FROM @X.nodes('/root/s') T(c)

select * from @userData

Here is the output:

image

 

SQL Reports – Can’t access SQL Reports (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)


ERROR

[UnauthorizedAccessException: Access is denied. (Exception from HRESULT:
 0x80070005 (E_ACCESSDENIED))]

 [FileLoadException: Could not load file or assembly
 'System.Web.RegularExpressions, Version=2.0.0.0, Culture=neutral,
 PublicKeyToken°3f5f7f11d50a3a' or one of its dependencies. Access is
 denied.]
 System.Web.Configuration.RegexWorker..cctor() +0

SOLUTION

Go to the Reporting Services Configuration Manager and restore the encryption key with your backup.

RSC-001

RSC-002
RSC-003

Apply the changes and it should fix the issue. If you didn’t back up the encryption key when you installed the reporting server… well, I guess you are out of luck. You will have to delete the key and start over by deleting the the corrupted key (Click the Delete button) and redefine the connection and subscriptions on the report server.

Now when you go to your reports via the url in the browser, it should work.

http://localhost/Reports/Pages/Folder.aspx

RSC-004

 

 

SQL Server – How to convert varchar to decimal

— This SQL reads in a varchar(10) field called work_hours that could have anything in it. It should be numeric represented as decimal.

— The CASE statement will produce either 0.00, or (if it is numeric) whatever is in the work_hours field.

— The is_numeric column is for analytical purposes and shows that the value of 1 means it was numeric and 0 when it was not numeric.

SELECT column1, column2,
--ISNUMERIC(work_hours) As is_numeric,
CASE
WHEN ISNUMERIC(work_hours) = 0 THEN 0
WHEN work_hours LIKE '%[^-+ 0-9]%' THEN 0
WHEN isnumeric(CAST(CAST(work_hours as varchar(10)) as decimal(10,2))) = 1 then CAST(CAST(work_hours as varchar(10)) as decimal(10,2))
WHEN CAST(work_hours AS NUMERIC(38, 0))
NOT BETWEEN -2147483648. AND 2147483647. THEN 0
ELSE
1
END AS work_hours

FROM table
WHERE column1 = 'doe'

If I had six rows of data where the work_hours had the following values:

image_01

Then this SQL will produce the results as follows:

image_02

 

 

SQL Server – Loop through an existing table and process a column with a delimited string

As mentioned in my last post (Loop through an existing table), I said that I would expound on its example by reading a column that contains a delimited string. In this case, it will be an address that is delimited by spaces. For example, 500 East Main St. This process will break down the address in parts and insert them into an address table that contains 10 columns, 1 column for each part.

The result will insert a row into the Address that looks like this:

NAGuid= 'A guid'
AddressPart1 = '500'
AddressPart1 = 'East'
AddressPart1 = 'Main'
AddressPart1 = 'St'
AddressPart1 = ''
AddressPart1 = ''
AddressPart1 = ''
AddressPart1 = ''
AddressPart1 = ''
AddressPart1 = ''

Here is the logic:

DECLARE @AddressTable Table (NAGuid uniqueidentifier, AddressPart1 varchar(20), AddressPart2 varchar(20), AddressPart3 varchar(20), AddressPart4 varchar(20), AddressPart5 varchar(20), AddressPart6 varchar(20), AddressPart7 varchar(20), AddressPart8 varchar(20), AddressPart9 varchar(20), AddressPart10 varchar(20))

DECLARE @maxrows INT
DECLARE @rowNum INT
DECLARE @NameAddressGUID uniqueidentifier
DECLARE @LastName varchar(50)
DECLARE @FirstName varchar(50)
DECLARE @MiddleName varchar(50)
DECLARE @TheAddressString varchar(300) = ''

DECLARE @Item VARCHAR(1000)
DECLARE @Position INT
DECLARE @Loop BIT

DECLARE @Address1 char(20) = ''
DECLARE @Address2 char(20) = ''
DECLARE @Address3 char(20) = ''
DECLARE @Address4 char(20) = ''
DECLARE @Address5 char(20) = ''
DECLARE @Address6 char(20) = ''
DECLARE @Address7 char(20) = ''
DECLARE @Address8 char(20) = ''
DECLARE @Address9 char(20) = ''
DECLARE @Address10 char(20) = ''

SELECT @maxRows = COUNT(*)
FROM   NameAddress

SELECT TOP 1 @NameAddressGUID = NameAddress_GUID,
             @LastName = LName,
             @FirstName = FName,
             @MiddleName = MName,
             @TheAddressString    = Address
        FROM NameAddress
    ORDER BY NameAddress_GUID ASC

SET @rowNum = 0

-- Loop until last row is reached
WHILE @rowNum < @maxRows
  BEGIN
      SET @rowNum = @rowNum + 1

       --This is where you can now do something to the record
      IF(right(@TheAddressString, 1) <> ' ' and Len(@TheAddressString) > 0)
		BEGIN
			Set @TheAddressString = @TheAddressString + ' '
		END 

		DECLARE @TheAddressStringCount int = 0
		SET @Loop = CASE WHEN LEN(@TheAddressString) > 0 THEN 1 ELSE 0 END
		WHILE (SELECT @Loop) = 1
			BEGIN
				SELECT @Position = CHARINDEX(' ', @TheAddressString, 1)	
				   IF(@Position > 0)
						BEGIN
							SELECT @Item = SUBSTRING(@TheAddressString, 1, @Position - 1)
							SELECT @TheAddressString = SUBSTRING(@TheAddressString, @Position + 1, LEN(@TheAddressString) - @Position)
							--Put charge into charges variable
							SET @TheAddressStringCount =  @TheAddressStringCount + 1
							IF @TheAddressStringCount = 1 SET @Address1 = @Item
							IF @TheAddressStringCount = 2 SET @Address2 = @Item
							IF @TheAddressStringCount = 3 SET @Address3 = @Item
							IF @TheAddressStringCount = 4 SET @Address4 = @Item
						    IF @TheAddressStringCount = 5 SET @Address5 = @Item
							IF @TheAddressStringCount = 6 SET @Address6 = @Item
							IF @TheAddressStringCount = 7 SET @Address7 = @Item
							IF @TheAddressStringCount = 8 SET @Address8 = @Item
							IF @TheAddressStringCount = 9 SET @Address9 = @Item
							IF @TheAddressStringCount = 10 SET @Address10 = @Item
						END
					ELSE
						BEGIN
							INSERT INTO @AddressTable (NAGuid, AddressPart1, AddressPart2, AddressPart3, AddressPart4, AddressPart5, AddressPart6, AddressPart7, AddressPart8, AddressPart9, AddressPart10)
							   SELECT @NameAddressGUID, @Address1, @Address2, @Address3, @Address4, @Address5, @Address6, @Address7, @Address8, @Address9, @Address10
							SET @Address1 = ''
							SET @Address2 = ''
							SET @Address3 = ''
							SET @Address4 = ''
							SET @Address5 = ''
							SET @Address6 = ''
							SET @Address7 = ''
							SET @Address8 = ''
							SET @Address9 = ''
							SET @Address10 = ''							
							SET @TheAddressString = ''
							SET @Item = ''
							SET @Position = 0
							SET @Loop = 0
						END
			END

       --Read the next row making sure the ID of the next row
       --is greater than previous row
	  SELECT TOP 1 @NameAddressGUID = NameAddress_GUID,
             @LastName = LName,
             @FirstName = FName,
             @MiddleName = MName,
             @TheAddressString    = Address
	    FROM NameAddress
	   WHERE NameAddress_GUID > @NameAddressGUID
    ORDER BY NameAddress_GUID ASC
END

SELECT * FROM @AddressTable

SQL Server – Loop through an existing table

In T-SQL you may want to loop through an existing table and do something. You may want want to use this type of processing in a SSRS report.

Here is an example of walking through a table that simply extracts a person’s first, last, and middle name. I didn’t do anything after the read, but simply added a display that shows the current row in the process. I will post an updated version that shows how to break down a column that is delimited and insert each section into its own column in another table.

Here is the code:

DECLARE @maxrows INT
DECLARE @rowNum INT
DECLARE @NameAddressGUID uniqueidentifier
DECLARE @LastName varchar(50)
DECLARE @FirstName varchar(50)
DECLARE @MiddleName varchar(50)

SELECT @maxRows = COUNT(*)
FROM NameAddress

SELECT @maxrows

SELECT TOP 1 @NameAddressGUID = NameAddress_GUID,
             @LastName = LName,
             @FirstName = FName,
             @MiddleName = MName
FROM NameAddress
ORDER BY NameAddress_GUID ASC

SET @rowNum = 0

-- Loop until last row is reached
WHILE @rowNum < @maxRows
BEGIN
SET @rowNum = @rowNum + 1

--This is where you can now do something to the record
SELECT 'YOU ARE ON ROW ', @rowNum

--Read the next row making sure the ID of the next row
--is greater than previous row
SELECT TOP 1 @NameAddressGUID = NameAddress_GUID,
             @LastName = LName,
             @FirstName = FName,
             @MiddleName = MName
FROM NameAddress
WHERE NameAddress_GUID > @NameAddressGUID
ORDER BY NameAddress_GUID ASC

END

SQL Server – Finding duplicate records

Finding rows on a table is straight forward when filtering on columns, but what if you want to find duplicate data.

Let’s say I have a table that represents parents and children. The key to the table is the parent guid which is unique. Each row also has a child guid key that unique to the child, but is not unique in the sense that the child could have accidentally been added to the table twice.

Let’s look at the table:

SELECT * FROM parent_child

1

As you can see, David Doe was entered twice. How do you narrow your query to group like rows?

SELECT CHILD_GUID, FIRSTNAME, LASTNAME
FROM parent_child
GROUP BY CHILD_GUID, FIRSTNAME,LASTNAME

The SQL statement is selecting those columns that would be unique among duplicated rows. These columns are then added to a ‘GROUP BY’ clause to group like rows together. As you can see above, David Doe is in the table twice. We want to see David Doe once. The results are as follows:

3

But now you only want to return rows that are duplicate only. This is where the ‘HAVING’ clause comes in to play. By adding HAVING COUNT(*) > 1, only rows that are duplicated more than once will return in the result set.

SELECT CHILD_GUID, FIRSTNAME, LASTNAME
FROM parent_child
GROUP BY CHILD_GUID, FIRSTNAME,LASTNAME
HAVING COUNT(*) > 1

2

Now we see only the duplicate records, but you can take it a step further by saying show me the duplicates of the duplicate records. By placing the latter SQL code into a temporary table, you can then select the temporary table and join the original table to it where the child guid’s are equal as follows:

DECLARE @ParentChild TABLE
 (child_guid uniqueidentifier, FIRSTNAME varchar(50), LASTNAME varchar(50))
INSERT INTO @ParentChild(child_guid, FIRSTNAME, LASTNAME)
SELECT CHILD_GUID, FIRSTNAME, LASTNAME
FROM parent_child
GROUP BY CHILD_GUID, FIRSTNAME,LASTNAME
HAVING COUNT(*) > 1

SELECT parent_child.PARENT_GUID, temptable.child_guid, 
       temptable.FIRSTNAME, temptable.LASTNAME
FROM @ParentChild temptable
LEFT OUTER JOIN parent_child
     on parent_child.CHILD_GUID = temptable.child_guid

The results are as follows:

11