Query to check normalization logic

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query to check normalization logic

Author

Topic

Codesearcher
Starting Member

8 Posts

Posted - 2011-08-30 : 06:09:38

I have a parent table BASE with below structure
ISN Col1 COl2 Col3
1 cmntsX1 cmntsX2 CmntsX3
2 cmntsY1 CmntsY2 NULL

This will be loaded into the child table as
ISN ColAll Count arraycolumn
1 cmntsX1 3 0
1 cmntsX2 3 1
1 cmntsX3 3 2
2 cmntsY1 2 0
2 cmntsY2 2 1

For ISN 1 i have value for all 3 columns so count for ISN 1 will be 3 & arraycolumn 0 to 2
similarly for ISN 2 there are 2 entries for col1 & col2 so count in child table would become 2 & arraycolumn as 0 & 1

Now i need to validate if the columns from base table are converted into rows in child table using a SQL query.Can any1 help in framing this query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 08:10:10

something like below should work:-


SELECT u.ISN,u.Val AS ColAll,
COUNT(1) OVER (PARTITION BY u.ISN) AS [Count],
ROW_NUMBER() OVER (PARTITION BY u.ISN ORDER BY u.Val ASC)-1 AS arraycolumn
FROM Yourtable
UNPIVOT (Val FOR Col IN (Col1,Col2,Col3))u

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources