CODEWITHSUNDEEP
sqlsql-servert-sql
jest
jest

Reputation: 13

Add rows based on a column value

I am having issues with creating additional rows based on a column value.

If my PageCount = 3 then I would need to have 2 additional rows where PONo is repeated but the ImagePath is incremented by 1 for each new row.

I am able to get the first row but, creating the additional rows with the ImagePath incremented by 1 is where I am stuck.

My result:
enter image description here

Expected result:
enter image description here

Finished: finished values

Current Select statement:

        SELECT PO, CASE WHEN LEFT(u.Path,3)= 'M:\' THEN '\\ServerName\'+RIGHT(u.Path,LEN(u.Path)-3) ELSE u.Path END AS [Imagepath],PAGECOUNT
FROM OPENQUERY([LinkedServer],'select * from data.vw_purchasing_docs_unc') AS u INNER JOIN
OPENQUERY([LinkedServer],'select * from data.purchasing_docs') AS d ON u.docid=d.docid
WHERE (CONVERT(VARCHAR(10),d.STATUS_DATE,120)=CONVERT(VARCHAR(10),GETDATE(),120))

batch file:

   bcp "select d.DOCID,DOC_TYPE,PO,d.STATUS, CASE WHEN LEFT(Path,3)= 'M:\' THEN '\\ServerName'+RIGHT(DWPath,LEN(Path)-3) ELSE Path END AS ImagePath, STATUS_DATE,'No' AS dwimport from openquery([LinkedServer],'select * from data.vw_purchasing_docs_unc') as u INNER JOIN openquery([LinkedServer],'select * from dwdata.purchasing_docs') as d ON u.docid=d.docid WHERE (CONVERT(varchar(10),STATUS_DATE,120)=CONVERT(varchar(10),GETDATE(),120)) AND d.STATUS IN ('FILED - Processing Complete','FILED - Partial Payment','FILED - Confirming') AND DOC_TYPE IN ('CO = Change Order','Purchase Order','CP = Capital Projects','Change Order','PO = Purchase Order','PO','PR = General Operating')" queryout "E:\Data\PO Trigger CSV\PO_Trigger_Doc.csv" -r \n -T -c -t"," -Umv -Smtvwrtst -Pm -q -k

Upvotes: 1

Views: 129

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28890

Select ponumber,b.rplc,pagecount
from table t
cross apply
(select replace(imagepath,'f'+cast(n-1) as varchar(100),'f0') as rplc from numbers n where n<=t.pagecount)b

To create numbers table,if you are wondering why you need it.Look here

CREATE TABLE Number (N INT IDENTITY(1,1) PRIMARY KEY NOT NULL);
GO

INSERT INTO Number DEFAULT VALUES;
GO 10000

Using your select statement after update:

;With cte(ponumber,imagepath,pagecount)
as
SELECT PO, CASE WHEN LEFT(u.Path,3)= 'M:\' THEN '\\ServerName\'+RIGHT(u.Path,LEN(u.Path)-3) ELSE u.Path END AS [Imagepath],PAGECOUNT
FROM OPENQUERY([LinkedServer],'select * from data.vw_purchasing_docs_unc') AS u INNER JOIN
OPENQUERY([LinkedServer],'select * from data.purchasing_docs') AS d ON u.docid=d.docid
WHERE (CONVERT(VARCHAR(10),d.STATUS_DATE,120)=CONVERT(VARCHAR(10),GETDATE(),120))
)
select Ponumber,b.rplc,pagecount from cte c
cross apply
 (select replace(imagepath,'f'+cast((n-1) as varchar(100)),'f0') as rplc from numbers n where n<=c.pagecount)b

Upvotes: 3

Paweł Dyl
Paweł Dyl

Reputation: 9143

If you would like to avoid additional table, you can use CTE:

WITH Images AS
(
  SELECT * FROM (VALUES
  ('C:\Folder', 2),
  ('D:\Folder', 3)) T(ImagePath, Val)
), Numbers AS
(
    SELECT * FROM (VALUES (1),(2),(3),(4)) T(N)
    UNION ALL
    SELECT N1.N*4+T.N N FROM (VALUES(1),(2),(3),(4)) T(N) CROSS JOIN Numbers N1
    WHERE N1.N*4+T.N<=100
)
SELECT ImagePath + '\f' + CONVERT(nvarchar(10) ,ROW_NUMBER() OVER (PARTITION BY ImagePath ORDER BY (SELECT 1))) NewPath
FROM Images
CROSS APPLY (SELECT TOP(Val) * FROM Numbers)  T(N)

Images is your source table. It can be anything, i.e. OPENQUERY. It produces:

NewPath
-------
C:\Folder\f1
C:\Folder\f2
D:\Folder\f1
D:\Folder\f2
D:\Folder\f3

Upvotes: 0

Related Questions