How can run second query based on first query?

user_6019515 0
Viewed 1895

enter image description hereIm using two query's, 1st separated one column to two columns and inserted one table and second query (PIVOT) fetching based on inserted table,

1st Query,

SELECT A.MDDID, A.DeviceNumber,  
          Split.a.value('.', 'VARCHAR(100)') AS MetReading
      FROM  (
             SELECT MDDID,DeviceNumber,  
                    CAST ('<M>' + REPLACE(Httpstring, ':', '</M><M>') + '</M>' AS XML) AS MetReading  
               FROM  [IOTDBV1].[dbo].[MDASDatas] E
               Where E.MDDID = 49101
             ) AS A CROSS APPLY MetReading.nodes ('/M') AS Split(a);

2nd Query

SELECT * FROM 
        (
           Select ID,MDDID,DeviceNumber,ReceivedDate
            , ROW_NUMBER() OVER(PARTITION BY ID ORDER BY (SELECT 1)) AS ID2
            , SPLT.MR.value('.','VARCHAR(MAX)') AS LIST FROM ( 
                        Select ID,MDDID,DeviceNumber,ReceivedDate
                                    , CAST( '<M>'+REPLACE(MeterReading,',','</M><M>')+'</M>' AS XML) AS XML_MR 
                                    From [dbo].[PARSEMDASDatas] E
                                    Where E.MeterReading is Not Null
                                    )E
                                    CROSS APPLY  E.XML_MR.nodes('/M') AS SPLT(MR)
                                    )A
                                    PIVOT
                                    (
                                        MAX(LIST) FOR ID2 IN ([1],[2],[3],[4],[5],[6],[7],[8])
                                    )PV

I want 2nd query run based on first query no need to require table. any help would be appreciated.

tsql sql-server sql
2 Answers

Your question is not very clear... And it is a very good example, why you always should add a MCVE, including DDL, sample data, own attempts, wrong output and expected output. This time I do this for you, please try to prepare such a MCVE the next time yourself...

If I get this correctly, your source table includes a CSV column with up to 8 (max?) values. This might be solved much easier, no need to break this up in two queries, no need for an intermediate table and not even for PIVOT.

--create a mockup-table to simulate your situation (slightly shortened for brevity)

DECLARE @YourTable TABLE(ID INT,MDDID INT, DeviceNumber VARCHAR(100),MetReading VARCHAR(2000));
INSERT INTO @YourTable VALUES
 (2,49101,'NKLDEVELOPMENT02','DCPL,981115,247484,9409') --the character code and some numbers
,(3,49101,'NKLDEVELOPMENT02','SPPL,,,,,,,,')            --eigth empty commas
,(4,49101,'NKLDEVELOPMENT02','BLAH,,,999,,');           --A value somewhere in the middle

--The cte will return the table as is. The only difference is a cast to XML (as you did it too)

WITH Splitted AS
(
    SELECT ID
          ,MDDID
          ,DeviceNumber
          ,CAST('<x>' + REPLACE(MetReading,',','</x><x>') + '</x>' AS XML) AS Casted
    FROM @YourTable t
)
SELECT s.ID
      ,s.MDDID
      ,s.DeviceNumber
      ,s.Casted.value('/x[1]','varchar(100)') AS [1]
      ,s.Casted.value('/x[2]','varchar(100)') AS [2]
      ,s.Casted.value('/x[3]','varchar(100)') AS [3]
      ,s.Casted.value('/x[4]','varchar(100)') AS [4]
      ,s.Casted.value('/x[5]','varchar(100)') AS [5]
      ,s.Casted.value('/x[6]','varchar(100)') AS [6]
      ,s.Casted.value('/x[7]','varchar(100)') AS [7]
      ,s.Casted.value('/x[8]','varchar(100)') AS [8]
FROM Splitted s;

the result

ID  MDDID   DeviceNumber        1       2       3       4       5       6       7       8
2   49101   NKLDEVELOPMENT02    DCPL    981115  247484  9409    NULL    NULL    NULL    NULL
3   49101   NKLDEVELOPMENT02    SPPL                            
4   49101   NKLDEVELOPMENT02    BLAH                    999                     NULL    NULL

The idea in short:

Each CSV is tranformed to a XML similar to this:

<x>DCPL</x>
<x>981115</x>
<x>247484</x>
<x>9409</x>

Using a position predicate in the XPath, we can call the first, the second, the third <x> easily.

user_50892040

CTE: WITH common_table_expression is answer

you can prepare some data in first query and user in second 

 WITH cte_table AS
    (
        SELECT  *
        FROM sys.objects
    )
    SELECT *
    FROM cte_table
    where name  like 'PK%'
user_100649720
Related