How to insert the logical data for the below logic

Question

Hi team,I have logic like below ,I need to insert the records into my target table using the below logic,can some one please provide the logic
In my source table I have columns like this

firstpart,date, quantity,date1,quenyity2,date3, quenyity3-,-,-,-,--,-,-,-,-,date24,quentity24

,like this I will get some data in my source forecast table
So here for every "firstpart" I will get number of columns of data(date, quantity),from the forecast table ,and this data will be coming in the form of columns ,as I explained in the above, so total number of columns are 24 for every month for each "firstpart",and the data will be exist for 12/15/17 columns(date, quantity).
I need to convert all the(date, quantity) columns data into the rows based on the "firstpart" value ,and before inserting the data,I need to update the first row as "STA",and the last row is "FAU" ,how can we insert this 2 rows additional along with the (date, quantity) of columns data

Source Forecast table data
Columns
_-----------------

Firstpart,date, quantity

A1,1-1-2021,200
A1,2-1-2021,100
A1,3-1-2021,300
B1,1-1-2021,100
B2,2-1-2021,200
B3,3-1-2021,300

Desired output

Firstpart,date,quantity
A1,STA,
A1,1-1-2021,200
A1,2-1-2021,100
A1,3-1-2021,300
,
,
,
,
A1,FAU
B1,STA,
B1,1-1-2021,100
B1,2-1-2021,200
B1,3-1-2021,300
,
,
,
B1,FAU,

I hope the above requirement is understand, please let me know ,in case need any details.

Thank you in advance!

Answer 1

As your query will give the right results ,but you mention only one single data, quantity in your query .

That is because you did not provide any sample data yourself. If you want a fully tested query, the recommendation is that you provide the CREATE TABLE statements for your query and INSERT statements with sample data. We also need the expected result given the sample. Then we can easily copy this into a query window to develop a tested solution. Without that we may work with a simpler table like Echo did, or just give you an outline of the query like I did.

You don't seemed to have tried my solution. I thought it would be a very good fit given your original post, but when I read your comment I get less that I understand what you are looking for.

Answer 2

Check this logic:

select firstpart, 'STA', NULL
from SourceTable
union all
select firstpart, cast(date1 as varchar(max)), quantity1
from SourceTable
union all
select firstpart, cast(date2 as varchar(max)), quantity2
from SourceTable
. . .
union all
select firstpart, cast(date24 as varchar(max)), quantity24
from SourceTable
union all
select firstpart, 'FAU', NULL
from SourceTable
order by firstpart

Answer 3

SELECT st.firstpart, V.date, V.quantity
FROM  SourceTable st
CROSS APPLY (VALUES (0, 'STA', NULL), 
                    (1, convert(char(10), st.date1, 126), st.quantity1),
                    (2, convert(char(10), st.date2, 126), st.quantity2),
                    ...
                    (24, convert(char(10), st.date24, 126), st.quantity24),
                    (100, 'FAU', NULL)) AS V(rowno, date, quantity)
ORDER BY st.firstpart, V.rowno

This should be more efficient than Viorel's solution. Also, his solution relies on chance to get the ordering right.

Answer 4

Hi @Naresh y ,

Are your values B1, B2, B3? According to your expected output, I modified it to B1, B1, B1, please refer to the following code:

CREATE TABLE #Sourcetable  
(Firstpart varchar(15),[date] DATE, quantity INT)  
INSERT INTO #Sourcetable VALUES  
('A1','1-1-2021',200),  
('A1','2-1-2021',100),  
('A1','3-1-2021',300),  
('B1','1-1-2021',100),  
('B1','2-1-2021',200),  
('B1','3-1-2021',300)  
  
CREATE TABLE #Output(Firstpart varchar(15)  
,[date] varchar(15), quantity INT)  
  
  
DECLARE @i INT  
SET @i=0  
WHILE @i<=(SELECT MAX(rr) FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY Firstpart ORDER BY Firstpart) rr  
FROM #Sourcetable) t)  
BEGIN  
   INSERT INTO #Output   
   SELECT Firstpart,[date], quantity   
   FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY Firstpart ORDER BY Firstpart) rr  
         FROM #Sourcetable) t  
   WHERE rr=@i+1  
  
   SET @i=@i+1  
END  
  
  
;WITH cte  
as(SELECT Firstpart,'0001-01-01' [date2],NULL quantity  
FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY Firstpart ORDER BY Firstpart) rr  
FROM #Sourcetable)t WHERE rr=1  
UNION ALL  
SELECT * FROM #Output  
UNION ALL  
SELECT Firstpart,'9999-12-31',NULL   
FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY Firstpart ORDER BY Firstpart) rr  
      FROM #Sourcetable)t WHERE rr=1)  
  
SELECT Firstpart,CASE WHEN [date2] = '0001-01-01' THEN 'STA'  
WHEN [date2] = '9999-12-31' THEN 'FAU' ELSE [date2] END [date], quantity   
FROM cte  
ORDER BY Firstpart,[date2], quantity   

Output:

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.