Knowledge Sharing of My Experience

USE [erplndb] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[fn_SplitString] ( @stringToSplit VARCHAR(MAX) ) RETURNS  @returnList TABLE ([Name] [nvarchar] (500)) AS BEGIN  DECLARE @name NVARCHAR(255)  DECLARE @pos INT  WHILE CHARINDEX(',', @stringToSplit) > 0  BEGIN   SELECT @pos  = CHARINDEX(',', @stringToSplit)     SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)   INSERT INTO @returnList    SELECT @name   SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)  END  INSERT INTO @returnList  SELECT @stringToSplit  RETURN  /***** EXAMPLE :: SELECT * FROM dbo.fn_SplitString('91,12,65,78,56,789') ******/ END

Create Table CREATE TABLE [dbo].[table1]( [main] [varchar](2) NULL, [child] [varchar](2) NULL, [qty] [int] NULL ) ON [PRIMARY] Insert Data INSERT INTO table1 VALUES ('A1','B1',1) INSERT INTO table1 VALUES ('B1','C1',2) INSERT INTO table1 VALUES ('B1','C2',3) INSERT INTO table1 VALUES ('C1','D1',4) SQL Query with dt_main as ( select 1 as level, main, child, qty from table1 where main = 'first-level' union all select level + 1 as level, a. main, a.child, a.qty   from table1 a inner join dt_main main on main.child = a.main ) select * from dt_main

Create Table  CREATE TABLE [dbo].[test01]( [id] [int] NOT NULL, [group_id] [int] NULL, [name] [varchar](50) NULL, [qty] [int] NULL, [newqty] [int] NULL,  CONSTRAINT [PK_test01] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Insert Data INSERT INTO [dbo].[test01] VALUES (1,1,'A',13,1) INSERT INTO [dbo].[test01] VALUES (2,1,'B',22,2) INSERT INTO [dbo].[test01] VALUES (3,1,'C',14,3) INSERT INTO [dbo].[test01] VALUES (4,2,'B',35,4) INSERT INTO [dbo].[test01] VALUES (5,2,'C',20,5) id group_id name qty newqty 1 1 A 13 1 2 1 B 22 2 3 1 C 14 3 4 2 B 35 4 5 2 C 20 5 Query select * from (select group_id,name,qty from test01) src pivot ( sum(qty) for name in ([A],[B],[C]) ) piv OR DECLARE @cols  AS VARCHAR(MAX), @query AS VARCHAR(MAX) selec