SQL12 Generate duplicate records as per the count of sequence

Want to generate dupkciate ID in sequence, 

For 1 , generate 1 record

For 2 , generate 2 records

For 3 , generate 3 records

For 4 , generate 4 records

and so on

these records will be generated all in sequence.

Output Needed

id
1
2
2
3
3
3
4
4
4
4

--Oracle

WITH SQL12
AS (
	SELECT LEVEL col
	FROM dual connect BY LEVEL < 5
	)
SELECT a.*
FROM SQL12 a
JOIN SQL12 b ON b.col <= a.col;

--SQL Server
WITH SQL12 (N)
AS (
	SELECT 1
	
	UNION ALL
	
	SELECT N + 1
	FROM SQL12
	WHERE N < 4
	)
SELECT a.*
FROM SQL12 a
JOIN SQL12 b ON b.N <= a.N
ORDER BY 1;

Create Single Line Border Table HTML/CSS

I used table tag in html to create a table, but by default this tab create table with double line border and extended to the full length of Page.

But my requirement to use single line table. You can use below properties for TABLE tag to create single line border table.

<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse;" width="200px">

Sample table created (click here)

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;

py0022 : 'python' is not recognized as an internal or external command,

C:\Users\******\AppData\Local\Programs\Python\Python37-32\Scripts>python --version

'python' is not recognized as an internal or external command,

operable program or batch file.

Above error doesnt mean Python is not installed on your system. Python may or may not installed on your system. You can check this manually. Go to C drive search for python folder.

Default installation can be under path "C:\Users\******\AppData\Local\Programs\Python"

Foe me this command worked under path 

C:\Users\*****\AppData\Local\Programs\Python\Python37-32>python --version

Python 3.7.2