Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Ntext Column Problem

In this puzzle you have to group by data based on id and Data column. Please check out the sample input values and sample expected output below.

Sample Input

Id Data Vals
1 a 12
1 a 10
1 a 12
1 b 100

Expected Output

Id Data Vals
1 a 34
1 b 100

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE ManageData
(
     Id INT
	,Data Ntext
	,Vals INT
)
GO
 
INSERT INTO ManageData VALUES
(1,'a',12),
(1,'a',10),
(1,'a',12),
(1,'b',100)
GO
 			 
--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution 1


--
 
SELECT Id,CAST(Data AS VARCHAR(MAX)) Data,SUM(Vals)Vals FROM ManageData
GROUP BY Id,CAST(Data AS VARCHAR(MAX))

--

** Note 1- ** – If you do the grouping without casting the column you will receive below error.

/*————————
SELECT Id,Data,SUM(Vals)Vals FROM ManageData
GROUP BY Id,Data
————————*/
Msg 306, Level 16, State 2, Line 2
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

** Note 2- ** – You cannot use text, ntext, and image data types in group by, order and not even replace.

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements