SQL Server blocked access to procedure 'sys.xp_cmdshell' of component

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

Solution

EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO



hide again (Rollback)
-- show advanced options
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
-- enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
-- hide advanced options
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

ALTER TABLE statement conflicted with the FOREIGN KEY constraint

Server Type - MS SQL  Server

Scanerio : Error faced when tried to apply foreign key constraint on table having data in it (non empty table).

Error

Msg 547, Level 16, State 0, Line 20 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "". The conflict occurred in database "", table "dbo.", column '**'

Solution

Empty table first then apply foreign key

 

SQL Server Database in Single User Model

 Some time you may have notice database status change to "Single User", Once database status changed to "single user" you will not be able to execute query on that database. Below is the solution to change the status of database to "MULTI_USER"

Solution :
SELECT request_session_id, * FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID('databaseName')

Connect to Master database or any other database which is in open state, Copy request_session_id from above query and Kill the session using below command.

KILL request_session_id

USE master
GO
ALTER DATABASE databaseName SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE databaseName SET online
Go

Function Identify Special Character and Replace

 Create function in sqlserver to identify the special character in string ( in parameter), and it will replace all the special character with "AND". You can modify the function as per your requirement and can replace it with space.

DROP FUNCTION func_Gen_str

CREATE FUNCTION Func_Gen_str (@NewStr VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
	DECLARE @newstring VARCHAR(1000) = @NewStr;
	DECLARE @pattern VARCHAR(100) = '%[^"0-9a-zA-Z'' -]%';
	DECLARE @i INT;

	BEGIN
		SET @i = PATINDEX(@pattern, @newstring)

		WHILE @i <> 0
		BEGIN
			SET @newstring = LEFT(@newstring, @i - 1) + '" AND "' + SUBSTRING(@newstring, @i + 1, 1000);
			SET @i = PATINDEX(@pattern, @newstring)
		END

		RETURN @newstring;
	END;
END
GO

SELECT dbo.Func_Gen_str('a,bcde.fghifhj')

result will be 
a " AND " bcde " AND " fghifhj

SSMS SQL Server Management Studio, close automatically when execute Query

SSMS SQL Server Management Studio, close automatically when executed Larze size query in Query Window.

It donot give any warning, just close all the opened query window, even it will not ask you to save the open Query Window

I traces the Error from Events: see below:
Faulting application name: Ssms.exe, version: 2011.110.2100.60, time stamp: 0x4f35e2d9
Faulting module name: Microsoft.SqlServer.Management.SqlParser.ni.dll, version: 11.0.2100.60, time stamp: 0x4f35e255
Exception code: 0xc00000fd
Fault offset:
Faulting process id:
Faulting application start time: 0x01d7771d1009d970
Faulting application path: C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe
Faulting module path: C:\Windows\assembly\NativeImages_v4.0.30319_32\Microsoft.S1e156a8e#\7884276fc39356973c434ef7a131480a\Microsoft.SqlServer.Management.SqlParser.ni.dll

What the Query is?

So Query is select statement contains 25000 union all with single column, with no syntax error

How i Resolve the Issue:

My main motive is to insert records in table using Union ALL

And i converted query into Individual Insert statement with Select CTAS

And it works for me.

i read somewhere on internet, Intellisense is causing the problem of crashing

Search String in SQLServer in All databases all tables all columns

 Search String in SQLServer in All databases all tables all columns

 

-------------------------Part 1

--------------------------Part 1
 --------------------------Part 1
USE [master]
GO


create/alter PROC [dbo].[GlobalSearch]
AS
BEGIN
 
 
    IF OBJECT_ID('dbo.ListOfObj', 'U') IS NOT NULL
    DROP TABLE dbo.ListOfObj;
    CREATE TABLE master.dbo.ListOfObj (DatabaseNAME nvarchar(370), TableName nvarchar(3630), ColumnName nvarchar(3630))
	
    SET NOCOUNT ON
    DECLARE @DBname nvarchar(256),@TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET @DBname=''
    SET  @TableName = ''
   
    DECLARE @getDBName CURSOR
    SET @getDBName = CURSOR FOR    
	SELECT name FROM sys.databases  where database_id >6
    OPEN @getDBName
    FETCH NEXT
    FROM @getDBName INTO @DBName
    WHILE @@FETCH_STATUS = 0
    BEGIN
   ----------------------------------------
   ----------------------------------------
   
        declare @sql_v nvarchar(max)
        BEGIN
            SET @SQL_v = 'insert into master.dbo.ListOfObj select ''' + @DBName +''', t.name as table_name , c.name as column_name
                            from
                                ' + quotename(@DBName) +'.'+'sys.tables t '+
                                ' join ' + quotename(@DBName) +'.'+ 'sys.columns c on c.object_id = t.object_id  '+
                                ' join ' + quotename(@DBName) +'.'+ 'sys.types ty on ty.system_type_id = c.system_type_id'+
                            ' where type_desc = ''USER_TABLE''  
							and ty.name in (''text'',''varchar'',''char'',''nvarchar'',''nchar'')'
                            --modify below line to  search field datatype

            EXEC sp_executesql @SQL_v
            --PRINT @SQL_v
   
        END
   ----------------------------------------
   ----------------------------------------

   --print '-Blah-Blah-Blah' + @DBName
    FETCH NEXT FROM @getDBName INTO @DBName
    END
    CLOSE @getDBName
    DEALLOCATE @getDBName

    END
 GO




 --------------------------Part 2
 --------------------------Part 2
 --------------------------Part 2


create/alter PROC [dbo].[GlobalSearch_FinalCall]
(
    @SearchString nvarchar(100)
)
AS
BEGIN
   
    IF OBJECT_ID('dbo.FinalResultOutput', 'U') IS NOT NULL
    DROP TABLE dbo.FinalResultOutput;
    CREATE TABLE master.dbo.FinalResultOutput (DatabaseNAME nvarchar(370), 
	TableName nvarchar(3630), ColumnName nvarchar(370), ColumnValue nvarchar(3630))
   
   

    exec dbo.GlobalSearch

	
    declare @SearchString2 nvarchar(100);
    SET @SearchString2 = QUOTENAME('%' + @SearchString + '%','''')
	

    DECLARE @DatabaseName nvarchar(2000), @TableName nvarchar(2000), 
	@ColumnName nvarchar(2000)
    DECLARE @ListOfObj CURSOR
    SET @ListOfObj = CURSOR FOR    
	SELECT DatabaseName, TableName, ColumnName FROM master.dbo.ListOfObj
    OPEN @ListOfObj
    FETCH NEXT
    FROM @ListOfObj INTO @DatabaseName, @TableName, @ColumnName
    WHILE @@FETCH_STATUS = 0
    BEGIN
   ----------------------------------------
   ----------------------------------------
        
		declare @s nvarchar(max)
            BEGIN
              set  @s = (
                    'SELECT count(*) from ' + @DatabaseName +'..' + 
					@TableName + ' where ' + @ColumnName + ' like ''''%'+
					@SearchString+'%''' )
			 
  
               INSERT INTO FinalResultOutput
               EXEC
			   (
                    'SELECT distinct ''' + @DatabaseName +''',''' + @TableName + ''',''' 
					+ @ColumnName + ''',''' +@s +'''''
                    FROM [' + @DatabaseName+'].dbo.'+@TableName +
					'  WHERE ' + @ColumnName + ' LIKE ' + @SearchString2
                ) 
		

           
            END
   ----------------------------------------
   ----------------------------------------
    FETCH NEXT FROM @ListOfObj INTO @DatabaseName, @TableName, @ColumnName
    END
    CLOSE @ListOfObj
    DEALLOCATE @ListOfObj

    END
 GO
 
 exec [GlobalSearch_FinalCall] 'Test'
 select * from FinalResultOutput
 
 

SQL Server Installation Error

 TITLE: Microsoft SQL Server 2012  Setup
------------------------------

The following error has occurred:

Error while enabling Windows feature : NetFx3, Error Code : -2146498220 , Please try enabling Windows feature : NetFx3 from Windows management tools and then run setup again. For more information on how to enable Windows features , see http://go.microsoft.com/fwlink/?linkid=227143

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.2100.60&EvtType=0x681D636F%25401428%25401

------------------------------
BUTTONS:

OK
------------------------------

Problem - Dot Net framework 3.5 is not installed on your machine, or higher version is installed

Solution - Install Dot Net Framework 3.5


I tried the solution provided by Microsoft, but it doesn't helped me

.NET Framework 3.5 installation errors: 0x800F0906, 0x800F081F, 0x800F0907, 0x800F0922
"https://docs.microsoft.com/en-US/troubleshoot/windows-client/application-management/dotnet-framework-35-installation-error"

Enable some setting as given in above link
Then Open Control Panel
> Programs ans Features
> Turn Windows Feature On or Off
> Then click " .NetFramework 3.5( include .NET 2.0 and 3.0)"

These steps gives me below error and i follow some other steps

Then i tried to install DotnetFramework 3.5 manually

it throws error

Error Code 0x800F0954 (Windows 10)

Correct steps to install .NET framework manually (see below)

1. Right-click Start, and click Run
2. Type regedit.exe and click OK
3. Go to the following registry key:     HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU
4. In the right-pane, if the value named UseWUServer exists, set its data to 0
5. Exit the Registry Editor
6. Restart Windows.


After install .netframework3.5 repeat above steps and revert the setting to original

RESTART machine and you will be able to install the SQL Server

Procedure | Compare Field Count and Distinct Count From a Database

 This procedure is for SQL Server, please take a idea and feel free to modify and use this procedure as per your need. This Procedure giving 3 fields in Output, 1st field giving column Name, 2nd field giving total count of records in table, and 3rd field having distinct records of a field.

CREATE TABLE #tempCount (
	TableName NVARCHAR(500)
	, AllTableCount NVARCHAR(500)
	, ColumnDistinctCount NVARCHAR(500)
	)

ALTER PROCEDURE countOfAField
AS
DECLARE @table_name NVARCHAR(50)
	, @column_name NVARCHAR(50)
	, @SQL_v NVARCHAR(500)

DECLARE cur_col CURSOR
FOR
SELECT TABLE_NAME
	, column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name = 'XXXXX'

OPEN cur_col

FETCH NEXT
FROM cur_col
INTO @table_name
	, @column_name

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SQL_v = 'Insert into #tempCount 
					Select 
						''' + @table_name + ''' as TableName
						, count(*) as AllTableCount 
						,count(distinct ' + @column_name + ') AS ColumnDistinctCount 
					from  ' + @table_name

	PRINT @SQL_v

	EXEC sp_executesql @SQL_v

	FETCH NEXT
	FROM cur_col
	INTO @table_name
		, @column_name
END

SELECT *
FROM #tempCount

CLOSE cur_col

DEALLOCATE cur_col

EXEC countOfAField

SQL14 Commulative SUM of Sales Year wise

Data is given table, we have date along with the sales. We need to calculate commulative sum for every year given in table.

below are the various ways to perform this task

Data in Table 

Year	Sales
2019-09-01	10
2019-10-01	60
2019-11-01	20
2019-12-01	10
2020-01-01	30
2020-02-01	20
2020-03-01	80
2021-01-01	70
2021-02-01	30
2021-03-01	10

Expected Result

year	sales	Sum
2019-09-01	10	10
2019-10-01	60	70
2019-11-01	20	90
2019-12-01	10	100
2020-01-01	30	30
2020-02-01	20	50
2020-03-01	80	130
2021-01-01	70	70
2021-02-01	30	100
2021-03-01	10	110

Table Insert Query

SELECT *
INTO SQL14
FROM (
	SELECT '2019-09-01' AS MONTH
		, 10 AS SALES
	
	UNION ALL
	
	SELECT '2019-10-01' AS MONTH
		, 60 AS SALES
	
	UNION ALL
	
	SELECT '2019-11-01' AS MONTH
		, 20 AS SALES
	
	UNION ALL
	
	SELECT '2019-12-01' AS MONTH
		, 10 AS SALES
	
	UNION ALL
	
	SELECT '2020-01-01' AS MONTH
		, 30 AS SALES
	
	UNION ALL
	
	SELECT '2020-02-01' AS MONTH
		, 20 AS SALES
	
	UNION ALL
	
	SELECT '2020-03-01' AS MONTH
		, 80 AS SALES
	
	UNION ALL
	
	SELECT '2021-01-01' AS MONTH
		, 70 AS SALES
	
	UNION ALL
	
	SELECT '2021-02-01' AS MONTH
		, 30 AS SALES
	
	UNION ALL
	
	SELECT '2021-03-01' AS MONTH
		, 10 AS SALES
	) t

--oracle
SELECT MONTH
	, SALES
	, (
		SELECT SUM(SALES)
		FROM SQL14 I
		WHERE I.MONTH <= O.MONTH
			AND EXTRACT(YEAR FROM TO_DATE(O.MONTH, 'YYYY-MM-DD')) = EXTRACT(YEAR FROM TO_DATE(I.MONTH, 'YYYY-MM-DD'))
		) AS YEARTODATE_SALE
FROM SQL14 O;

--SQLServer Solution 1
SELECT year(month)
	, sum(sales) OVER (
		PARTITION BY year(month) ORDER BY month
		)
	, *
FROM sql14

--SQLServer Solution 2
SELECT MONTH
	, SALES
	, (
		SELECT SUM(SALES)
		FROM SQL14 I
		WHERE I.MONTH <= O.MONTH
			AND YEAR(O.MONTH) = YEAR(I.MONTH)
		) AS YEARTODATE_SALE
FROM SQL14 O;

--SQLServer Solution 3
WITH cte
AS (
	SELECT *
		, year(month) y
	FROM sql14
	)
SELECT DISTINCT *
FROM SQL14 c
CROSS APPLY (
	SELECT DISTINCT sum(sales) AS total
	FROM SQL14
	WHERE (c.month) >= (month)
		AND year(c.month) = year(month)
		--group by year(month)
	) t

--SQLServer Solution 4
SELECT t1.month
	, year(t1.month)
	, t1.sales
	, sum(t2.sales)
FROM sql14 t1
JOIN sql14 t2 ON (t1.month) >= (t2.month)
	AND year(t1.month) = year(t2.month)
GROUP BY t1.month
	, t1.sales
	, year(t1.month)
ORDER BY 1
	, 2

Tags- SQL Excercise, Requirement solution, Practice, Query Logic, Assignments, SQL Practice

Install SSMA/No SQL Server instance was found on this machine

Error : No SQL Server instance was found on this machine. Please try after installing an SQL Server instance.

Scenario : This error faced while installing SSMA on machine.

Correct Step to Install ( Below step are for oracle SSMA):

Download installer from link (click here) "https://www.microsoft.com/en-us/download/details.aspx?id=54258"

Download File - SSMAforOracle_x.xx.x.msi

Download File - SSMAforOracleExtensionPack_x.xx.x.msi

STEP 1

• Install SSMAforOracle_x.xx.x.msi on your computer/machine (after double click)
• Accept Agreements
• Click on "Typical" type installation 
• Click on "install" button

STEP 2

• Double click on SSMAforOracleExtensionPack_x.xx.x.msi
• Accept Agreements
• Click on "Typical" type installation 
• Click On "Install" Button
• Installation will finish, allow for any permission if it asked
• Popup will visible on screen after few seconds, you can see 2 option on screen
• Local instance (chose this option if SQL Server Engine is installed on local computer)
•   Remote instance ( Chose this option if SQL Server Engine is installed on remote machine, cloud, network computer)
• Add connection details (Hostname, port, username, password)
• Click check box "Trust server Certificate", if "Remote instance" option is selected
• on next Screen default option is selected " Install Untilities Database .....", Click on next 
• Let the installtion complete, after that you will be ready to use

Why error was occured : User was selecting option "Local Instance" while installation, however SQL Server engine was installed on remote machine

SQL13 How to Fill/populate data until next value

Scenerio is Data is populate in table but in between some null occurance are available. Need to populate the in null starting from copy from 1 populate data and copy until next occurance of data and so on.

This can be done by various ways, few of the best way, solution writing in this post. 

Data in Table 

id	COL1	COL2
1	A	B
2
3
4
5
6	A	C
7
8
9
10	A	D
11
12
13
14
15
16
17

Expected Result

id	COL1	COL2
1	A	B
2		B
3		B
4		B
5		B
6	A	C
7		C
8		C
9		C
10	A	D
11		D
12		D
13		D
14		D
15		D
16		D
17		D

SELECT *
INTO SQL13
FROM (
	SELECT 1 ID, 'A' COL1, 'B' COL2
	
	UNION ALL
	
	SELECT 2, NULL, NULL
	
	UNION ALL
	
	SELECT 3, NULL, NULL
	
	UNION ALL
	
	SELECT 4, NULL, NULL
	
	UNION ALL
	
	SELECT 5, NULL, NULL
	
	UNION ALL
	
	SELECT 6 ID, 'A' COL1, 'C' COL2
	
	UNION ALL
	
	SELECT 7, NULL, NULL
	
	UNION ALL
	
	SELECT 8, NULL, NULL
	
	UNION ALL
	
	SELECT 9, NULL, NULL
	
	UNION ALL
	
	SELECT 10 ID, 'A' COL1, 'D' COL2
	
	UNION ALL
	
	SELECT 11, NULL, NULL
	
	UNION ALL
	
	SELECT 12, NULL, NULL
	
	UNION ALL
	
	SELECT 13, NULL, NULL
	
	UNION ALL
	
	SELECT 14, NULL, NULL
	
	UNION ALL
	
	SELECT 15, NULL, NULL
	
	UNION ALL
	
	SELECT 16, NULL, NULL
	
	UNION ALL
	
	SELECT 17, NULL, NULL
	) test

--Solution 1
SELECT *
	, CASE 
		WHEN COL2 IS NULL
			THEN (
				SELECT TOP 1 COL2
				FROM SQL13
				WHERE ID < T.ID
					AND COL2 IS NOT NULL
				ORDER BY ID DESC
		    	)
		ELSE COL2
		END AS COL3
FROM SQL13 T
--Solution 2
WITH CTE AS (
	SELECT ID
		, COL2
		, COALESCE(LEAD(ID) OVER (
				ORDER BY ID
				), (
				SELECT COUNT(*) + 1
				FROM SQL13
				)) NXT_SEQ
	FROM SQL13
	WHERE COL1 IS NOT NULL
	)

SELECT EP.ID
	, EP.COL1
	, EP.COL2
	--,EP.COL3
	, (
		SELECT CTE.COL2
		FROM CTE
		WHERE EP.ID >= CTE.ID
			AND EP.ID < CTE.NXT_SEQ
		) POPULATED
FROM SQL13 EP;

--Solution 3
SELECT c1.id AS id
	, coalesce(c1.col2, t.col2)
FROM SQL13 c1
OUTER APPLY (
	SELECT TOP 1 c2.col2
	FROM SQL13 c2
	WHERE c1.col2 IS NULL
		AND c2.col2 IS NOT NULL
		AND c2.id < c1.id
	ORDER BY c2.id DESC
	) t

Tags- SQL Excercise, Requirement solution, Practice, Query Logic, Assignments, SQL Practice

SQL11 Generate Duplicate Data to Some specific count

We have given data in table, along with the count of quantity with respect to each order and item, we need to generate number of rows equal to the count of QTY given in table.

Assume, for first row 5 count is given, as a output we need to generate 1 row 5 time as duplicate row.

Data in Table 

orderid	item	qty
o1	A1	5
o2	A2	1
o3	A3	3

Output Needed

orderid	item	qty
o1	A1	1
o1	A1	2
o1	A1	3
o1	A1	4
o1	A1	5
o2	A2	1
o3	A3	1
o3	A3	2
o3	A3	3

SELECT *
INTO SQL11
FROM (
	SELECT 'o1' OrderID
		,'A1' Item
		,5 QTY
	
	UNION ALL
	
	SELECT 'o2'
		,'A2'
		,1
	
	UNION ALL
	
	SELECT 'o3'
		,'A3'
		,3
	) t1

Solution 1

WITH t
AS (
	SELECT orderid
		,item
		,qty
	FROM SQL11
	
	UNION ALL
	
	SELECT orderid
		,item
		,qty - 1
	FROM t
	WHERE qty > 1
	)
SELECT *
FROM t
ORDER BY 1
	,3

note* - abvove query can also be possible to execute in Oracle, before execute in oracle, change the with clause "with t (orderid, item, qty) as ("

Oracle Specific Query

SELECT OrderID
	,Item
	,1
FROM (
	SELECT DISTINCT OrderID
		,Item
		,LEVEL
	FROM SQL11 EO connect BY LEVEL <= (
			SELECT QTY
			FROM SQL11 EI
			WHERE EO.OrderID = EI.OrderID
			)
	)
ORDER BY 1
	,2
	,3;

SQL10 SQL Excercise, Requirement solution, Practice, Query Logic

Data in Table 

order_Day	order_id	prod_id	quantity	price
01-jul-2011	o1	p1	5	5
01-jul-2011	o2	p2	2	10
01-jul-2011	o3	p3	10	25
01-jul-2011	o4	p1	20	5
02-jul-2011	o5	p3	5	25
02-jul-2011	o6	p4	6	20
02-jul-2011	o7	p1	2	5
02-jul-2011	o8	p5	1	50
02-jul-2011	o9	p6	2	50
02-jul-2011	o10	p2	4	10

Output Needed

order_Day	prod_id	Sum
01-jul-2011	p3	250
02-jul-2011	p3	125

SELECT *
INTO SQL10
FROM (
	SELECT '01-jul-2011' order_Day
		,'o1' order_id
		,'p1' prod_id
		,5 quantity
		,5 price
	
	UNION ALL
	
	SELECT '01-jul-2011'
		,'o2'
		,'p2'
		,2
		,10
	
	UNION ALL
	
	SELECT '01-jul-2011'
		,'o3'
		,'p3'
		,10
		,25
	
	UNION ALL
	
	SELECT '01-jul-2011'
		,'o4'
		,'p1'
		,20
		,5
	
	UNION ALL
	
	SELECT '02-jul-2011'
		,'o5'
		,'p3'
		,5
		,25
	
	UNION ALL
	
	SELECT '02-jul-2011'
		,'o6'
		,'p4'
		,6
		,20
	
	UNION ALL
	
	SELECT '02-jul-2011'
		,'o7'
		,'p1'
		,2
		,5
	
	UNION ALL
	
	SELECT '02-jul-2011'
		,'o8'
		,'p5'
		,1
		,50
	
	UNION ALL
	
	SELECT '02-jul-2011'
		,'o9'
		,'p6'
		,2
		,50
	
	UNION ALL
	
	SELECT '02-jul-2011'
		,'o10'
		,'p2'
		,4
		,10
	) t

Solution 1

SELECT *
FROM (
	SELECT ps.order_day
		,ps.prod_id
		,accum_sum
		,row_number() OVER (
			PARTITION BY ps.order_day ORDER BY accum_sum DESC
			) rn
	FROM SQL10 ps
	JOIN (
		SELECT quantity * price total_amount
			,order_day
			,order_id
			,sum(quantity * price) OVER (
				PARTITION BY order_day
				,prod_id ORDER BY order_day
					,prod_id
				) accum_sum
		FROM SQL10
		) ps1 ON ps.order_day = ps1.order_day
		AND ps.order_id = ps1.order_id
	) t
WHERE rn = 1

Solution 2

SELECT Order_Day

	,PrOd_id
	,SOLD_AMT
FROM (
	SELECT Order_Day
		,PrOd_id
		,SUM(quantity * Price) SOLD_AMT
		,DENSE_RANK() OVER (
			PARTITION BY Order_DaY ORDER BY SUM(quantity * Price) DESC
			) RNK
	FROM SQL10
	GROUP BY Order_Day
		,PrOd_id
	) t1
WHERE RNK = 1;

Semicolon, comma separated data to Rows

 If you have data in column in comma separated or semicolon separated and you would like to separate each one to a new row, it is pretty simple in SQL server. Use below query as per your requirement, right now this query is looking for semicolon as separator, and you modify the query as per requirement. Change the column names as per your table structure.

;WITH temp(ID, Newcolumn, Name) AS

(
    SELECT
        Id,
cast(LEFT(Name, CHARINDEX(';', Name + ';') - 1) as nvarchar(2000)),
        STUFF(Name, 1, CHARINDEX(';', Name + ';'), '')
    FROM 
YOURTABLENAME
where 
name like '%;%'
    UNION all
    SELECT
        Id,
cast(LEFT(Name, CHARINDEX(';', Name + ';') - 1) as nvarchar(2000)),
        STUFF(Name, 1, CHARINDEX(';', Name + ';'), '')
    FROM 
temp
    WHERE
        Name > ''
)
SELECT
    Newcolumn 
FROM 
temp 

Do not change the column name "Newcolumn" from query, as this column is key column for your query. will store all new data in this column.

how table data looks like for me:

YOURTABLENAME

id      Name

1       Andhra Pradesh; Arunachal Pradesh; Assam; Bihar; Chhattisgarh; Goa; Gujarat; Haryana; Himachal Pradesh; Jammu and Kashmir; Jharkhand; Karnataka; Kerala; Madhya Pradesh; Maharashtra; Manipur; Meghalaya; Mizoram; Nagaland; Odisha; Punjab; Rajasthan; Sikkim; Tamil Nadu; Telangana; Tripura; Uttar Pradesh; Uttarakhand; West Bengal; Andaman and Nicobar; Chandigarh; Dadra and Nagar Haveli; Daman and Diu; Lakshadweep; Delhi; Puducherry

2       Alabama;  Alaska;  American Samoa;  Arizona;  Arkansas;  California;  Colorado;  Connecticut;  Delaware;  District of Columbia;  Florida;  Georgia;  Guam;  Hawaii;  Idaho;  Illinois;  Indiana;  Iowa;  Kansas;  Kentucky;  Louisiana;  Maine;  Maryland;  Massachusetts;  Michigan;  Minnesota;  Minor Outlying Islands;  Mississippi;  Missouri;  Montana;  Nebraska;  Nevada;  New Hampshire;  New Jersey;  New Mexico;  New York;  North Carolina;  North Dakota;  Northern Mariana Islands;  Ohio;  Oklahoma;  Oregon;  Pennsylvania;  Puerto Rico;  Rhode Island;  South Carolina;  South Dakota;  Tennessee;  Texas;  U.S. Virgin Islands;  Utah;  Vermont;  Virginia;  Washington;  West Virginia;  Wisconsin;  Wyoming

How data will appear after executing query

1 Andhra Pradesh

1 Arunachal Pradesh

1 Assam

1 Bihar

1 Chhattisgarh

1 Goa

1 Gujarat

1 Haryana

1 Himachal Pradesh

1 Jammu and Kashmir

1 Jharkhand

1 Karnataka

1 Kerala

1 Madhya Pradesh

1 Maharashtra

1 Manipur

1 Meghalaya

1 Mizoram

1 Nagaland

1 Odisha

1 Punjab

1 Rajasthan

1 Sikkim

1 Tamil Nadu

1 Telangana

1 Tripura

1 Uttar Pradesh

1 Uttarakhand

1 West Bengal

1 Andaman and Nicobar

1 Chandigarh

1 Dadra and Nagar Haveli

1 Daman and Diu

1 Lakshadweep

1 Delhi

1 Puducherry

2 Alabama

2 Alaska

2 American Samoa

2 Arizona

2 Arkansas

2 California

2 Colorado

2 Connecticut

2 Delaware

2 District of Columbia

2 Florida

2 Georgia

2 Guam

2 Hawaii

2 Idaho

2 Illinois

2 Indiana

2 Iowa

2 Kansas

2 Kentucky

2 Louisiana

2 Maine

2 Maryland

2 Massachusetts

2 Michigan

2 Minnesota

2 Minor Outlying Islands

2 Mississippi

2 Missouri

2 Montana

2 Nebraska

2 Nevada

2 New Hampshire

2 New Jersey

2 New Mexico

2 New York

2 North Carolina

2 North Dakota

2 Northern Mariana Islands

2 Ohio

2 Oklahoma

2 Oregon

2 Pennsylvania

2 Puerto Rico

2 Rhode Island

2 South Carolina

2 South Dakota

2 Tennessee

2 Texas

2 U.S. Virgin Islands

2 Utah

2 Vermont

2 Virginia

2 Washington

2 West Virginia

2 Wisconsin

2 Wyoming

Concatenate Rows in SQL Server

In SQL Server if you would like to concatenate row to 1 single string, with using stuff, XML path aggregation, alternatively we can use T/SQL program.

I am preferring the TSQL cause of few reasons, first reason as before i was using XML Path aggregation and while using that method string truncation occurs at some limit. XML Path/stuff was not able to aggregate or concatenate all rows from table in single row.

Either we need to limit the number of rows while using above way.

in TSQL we can handle this situation, in that way we can play with length of charaters that need to concatenate.

How to use below TSQL:

first you guys need to populate your data in below table format, table name and column name should be exactly same.

Table Name - Table_Group

Column Name 1 - Column_id (will be using for group by )

Column Name 2 - column_text (that need to concatenate)

IF exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'data' AND TABLE_SCHEMA = 'dbo')

Drop table dbo.data;

create table data (id int, content nvarchar(max))

Declare

@out1 VARCHAR(max),

@Query_Part1 VARCHAR(max), 

@Query_Part2 VARCHAR(max), 

@Query_Part3 VARCHAR(max),

@Query_Part4 VARCHAR(max),

@Query_Final VARCHAR(max),

@row_num int

Declare

@column_id VARCHAR(400), 

@column_text VARCHAR(max) ,

@n int

set @Query_Part1 = '';

set @Query_Part2 = '';

set @Query_Part3 = '';

set @Query_Part4 = '';

set @Query_Final = '';

set @n = 0;

set @row_num = 0;

DECLARE CURSOR_C1 CURSOR FOR SELECT distinct column_id  FROM Table_Group  order by 1

OPEN CURSOR_C1

FETCH NEXT FROM CURSOR_C1 INTO @column_id 

WHILE @@FETCH_STATUS=0

BEGIN

DECLARE CURSOR_C2 CURSOR FOR 

SELECT column_text, row_number() over (order by column_id ) rn FROM Table_Group where column_id  = @column_id order by 2

OPEN CURSOR_C2

FETCH NEXT FROM CURSOR_C2 INTO @column_text , @row_num

WHILE @@FETCH_STATUS=0

BEGIN

IF isnull(LEN(@Query_Part2),0) < 1 AND (isnull(LEN(@Query_Part1),0) + isnull(len(@column_text),0)  ) < 32767 

set @Query_Part1 = @Query_Part1 + char(10)+ '-Line  ' +cast(@row_num as nvarchar)+ ' - '+char(10) +@column_text 

ELSE IF isnull(LEN(@Query_Part3),0) < 1 AND (isnull(LEN(@Query_Part2),0) + isnull(len(@column_text),0) ) < 32767 

set @Query_Part2 = @Query_Part2 +char(10)+ '-Line  ' +cast(@row_num as nvarchar)+ ' - '+char(10)  +@column_text 

ELSE IF isnull(LEN(@Query_Part4),0) < 1 AND (isnull(LEN(@Query_Part3),0)  + isnull(len(@column_text),0)) < 32767 

set @Query_Part3 = @Query_Part3 + char(10)+ '-Line  ' +cast(@row_num as nvarchar)+ ' - '+char(10)  +@column_text 

ELSE

set @Query_Part4 = @Query_Part4 +char(10)+ '-Line ' +cast(@row_num as nvarchar)+ ' - ' +char(10) +@column_text 

set @Query_Final = isnull(@Query_Part1,'')+isnull(@Query_Part2,'')+isnull(@Query_Part3,'')+isnull(@Query_Part4,'')

--print @Query_Final

FETCH NEXT FROM CURSOR_C2 INTO @column_text , @row_num

END

CLOSE CURSOR_C2;

DEALLOCATE CURSOR_C2;

insert into data values ( @column_id , @Query_Final) ;

set @Query_Final = ''

set @Query_Part1 = ''

set @Query_Part2 = ''

set @Query_Part3 = ''

set @Query_Part4 = ''

FETCH NEXT FROM CURSOR_C1 INTO @column_id;

set @row_num = 0 ;

END

CLOSE CURSOR_C1;

DEALLOCATE CURSOR_C1;

select *from data