locked
Normalized data RRS feed

  • Question

  • Situation:  Table is up with

    ParentID, DataTagID, SubTypeID, DataFloat, DataImage, DataInt, DataColumnID

    <GUID>       20                   3                 Null               Null             83.3            1

    <GUID>       19                    4                0.398            Null             Null              2

    …………

    I want to get that into a table that is easier to read and move to another system.  My idea was just to use a cursor  and extract the data from whatever is not null.. the table would like like

    <Named Columns from the ID of above table>         1<col1>                      2<col2>

                                                                                   83.3                              0.398

    Does anyone have any suggestions on doing this, or should I just stick my cursor idea?

    Thursday, September 15, 2016 6:38 PM

Answers

  • Ok, This is a completely different situation. You have an EAV model. You should have told use this. And this is your attribute-value table.

    Is this correct?

    But in the end it is still a pivot:

    DECLARE @Sample TABLE
        (
    	  EntityID INT,
          Datafloat FLOAT ,
          DataImage IMAGE ,
          DataInt BIGINT ,
          DataText VARCHAR(MAX) ,
          Label VARCHAR(100)
        )
    
    INSERT  INTO @Sample
    VALUES  ( 1, NULL, NULL, NULL, '8-26-16', 'Sample name' ),
            ( 1, NULL, NULL, NULL, '{8CD1F545-9118-42A3-B2F3-ECA6C59842AB}', 'Unique identifier of sample' ),
            ( 1, NULL, NULL, 636077973330000000, NULL, 'Sample created date' ),
            ( 1, NULL, NULL, 636077973330000000, NULL, 'Sample modified date' ),
            ( 1, NULL, NULL, 0, NULL, 'Excluded' );
    
    SELECT	S.EntityID, 
    		MIN(IIF(S.Label = 'Sample name', S.DataText, NULL)) AS [Sample name],
    		MIN(IIF(S.Label = 'Unique identifier of sample', S.DataText, NULL)) AS [Unique identifier of sample],
    		MIN(IIF(S.Label = 'Sample created date', S.DataInt, NULL)) AS [Sample created date],
    		MIN(IIF(S.Label = 'Sample modified date', S.DataInt, NULL)) AS [Sample modified date],
    		MIN(IIF(S.Label = 'Excluded', S.DataInt, NULL)) AS [Excluded]
    FROM	@Sample S
    GROUP BY	S.EntityID;

    Sunday, September 18, 2016 11:13 AM

All replies

  • I'm quite sure, that normalization is the wrong term here. It's more like a pivot. E.g.

    DECLARE @Sample TABLE
    (
    	ParentID UNIQUEIDENTIFIER, 
    	DataTagID INT, 
    	SubTypeID INT, 
    	DataFloat FLOAT, 
    	DataImage INT, 
    	DataInt INT, 
    	DataColumnID INT
    );
    
    INSERT INTO @Sample
    VALUES	( NULL, 20, 3,  NULL, NULL, 83.3, 1 ),
    		( NULL, 19, 4, 0.398, NULL, NULL, 2 );
    
    SELECT	S.ParentID, 
    		S.DataTagID, 
    		S.SubTypeID,  
    		S.DataImage,
    		IIF(S.DataColumnID = 1, S.DataFloat, NULL) AS [1],
    		IIF(S.DataColumnID = 2, S.DataInt, NULL) AS [2]
    FROM	@Sample S;

    Thursday, September 15, 2016 7:50 PM
  • maybe I am not understanding or was not clear...

    in my example

    ParentID, DataTagID, SubTypeID, DataFloat, DataImage, DataInt, DataColumnID

    <GUID>       20                   3                 Null               Null             83.3            1

    <GUID>       19                    4                0.398            Null             Null              2

    lets say there are 24 rows like this... DataColumnID is unique  That DataColumnId would acutally be the Column names in my new table... and from this example   the first row would be Col1(row1)= 83.3 Col2(row1) = 0.398, and I would keep going till I get to the end of the table 1.

    Thursday, September 15, 2016 8:46 PM
  • Why not posting a concise and complete example? Table DDL and sample data as runnable T-SQL script...
    • Edited by Stefan Hoffmann Thursday, September 15, 2016 8:56 PM
    • Marked as answer by LandLord324 Friday, September 16, 2016 1:30 PM
    • Unmarked as answer by LandLord324 Friday, September 16, 2016 7:04 PM
    Thursday, September 15, 2016 8:55 PM
  • You're still describing a pivot
    Thursday, September 15, 2016 9:02 PM
  • Hi LandLord324

    See the following code below

    DECLARE @Sample TABLE
    (
    	ParentID UNIQUEIDENTIFIER, 
    	DataTagID INT, 
    	SubTypeID INT, 
    	DataFloat FLOAT, 
    	DataImage INT, 
    	DataInt INT, 
    	DataColumnID INT
    );
    
    INSERT INTO @Sample
    VALUES	( NULL, 20, 3,  NULL, NULL, 83.3, 1 ),
    		( NULL, 19, 4, 0.398, NULL, NULL, 2 );
    
    
    
    Select * from 
    (
    	SELECT	S.ParentID, 
    			S.DataTagID, 
    			S.SubTypeID,  
    			S.DataImage,
    			S.DataFloat,
    			S.DataColumnID,
    			S.DataInt,
    			S.DataColumnID as DataColumnID2
    	FROM	
    		@Sample as S
    	
    
    ) as t1
    PIVOT
    (
    	sum(DataInt) for DataColumnID in ([1])
    
    )
     AS pvt2
    PIVOT
    (
    	sum(DataFloat) for DataColumnID2 in ([2])
    )
     AS pvt1
    
    
    
    
    
    
    
    
    

    Result

    Best Regards


    Ricardo Lacerda

    Thursday, September 15, 2016 9:06 PM
  • here is an image of the result of my sql result...

    Then new table would look like this

    Does make a little more sense?


    • Edited by LandLord324 Friday, September 16, 2016 7:03 PM Sorry.. didn't realize the pictures were not showing up here
    Friday, September 16, 2016 1:40 PM
  • Good day LandLord,

    If the issue still open and you did not get answer that solve your case, then please... let's end what I call a "guessing game". 95% of a good answer, is a well asked question, which include the information to reproduce the issue with samples. This is the basic of discussion and solution.

    In this case (if this is still open), please post:

    1) Queries to CREATE your table(s) including indexes
    2) Queries  to INSERT sample data.
    3) The desired result given the sample, as text or image of excel for example.
    4) A short description of the business rules, and how you got 1-2 of the results
    5) Which version of SQL Server you are using (this will help to fit the query to your version).

    Regards,


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, September 18, 2016 9:30 AM
  • Ok, This is a completely different situation. You have an EAV model. You should have told use this. And this is your attribute-value table.

    Is this correct?

    But in the end it is still a pivot:

    DECLARE @Sample TABLE
        (
    	  EntityID INT,
          Datafloat FLOAT ,
          DataImage IMAGE ,
          DataInt BIGINT ,
          DataText VARCHAR(MAX) ,
          Label VARCHAR(100)
        )
    
    INSERT  INTO @Sample
    VALUES  ( 1, NULL, NULL, NULL, '8-26-16', 'Sample name' ),
            ( 1, NULL, NULL, NULL, '{8CD1F545-9118-42A3-B2F3-ECA6C59842AB}', 'Unique identifier of sample' ),
            ( 1, NULL, NULL, 636077973330000000, NULL, 'Sample created date' ),
            ( 1, NULL, NULL, 636077973330000000, NULL, 'Sample modified date' ),
            ( 1, NULL, NULL, 0, NULL, 'Excluded' );
    
    SELECT	S.EntityID, 
    		MIN(IIF(S.Label = 'Sample name', S.DataText, NULL)) AS [Sample name],
    		MIN(IIF(S.Label = 'Unique identifier of sample', S.DataText, NULL)) AS [Unique identifier of sample],
    		MIN(IIF(S.Label = 'Sample created date', S.DataInt, NULL)) AS [Sample created date],
    		MIN(IIF(S.Label = 'Sample modified date', S.DataInt, NULL)) AS [Sample modified date],
    		MIN(IIF(S.Label = 'Excluded', S.DataInt, NULL)) AS [Excluded]
    FROM	@Sample S
    GROUP BY	S.EntityID;

    Sunday, September 18, 2016 11:13 AM