SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Order data , extra data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WJHamel
Aged Yak Warrior

USA
644 Posts

Posted - 09/21/2012 :  11:09:16  Show Profile  Reply with Quote
This could get ugly. I'll try to be as clear as possible.

The following table is a segment of another table i had to use a pivot on (the pivoted rows/columns):



CREATE TABLE [dbo].[abuestypedescpivoted](
	[1930] [int] NULL,
	[1924] [int] NULL,
	[1927] [int] NULL,
	[1933] [int] NULL,
	[1934] [int] NULL,
	[1928] [int] NULL,
	[1931] [int] NULL,
	[1925] [int] NULL,
	[1932] [int] NULL,
	[1926] [int] NULL,
	[1929] [int] NULL
) ON [PRIMARY]



The data in this table is being used to update fields in another table. In the destination table, i am only allowed to use three values total. There are entries in the source table where up to four or more columns contain data. My task is to first identify the rows where more than three columns have values, then, in numeric order of the column names above, use the first three columns which contain data. I would like to pull the extra columns out to another new table to create a concatenated narrative from. So, how do i first find the rows with more than three columns of data, and then order those by the column values (name of the column) and only use the three lowest values and move the extra data to the new table for the other task?


1930	1924	1927	1933	1934	1928	1931	1925	1932	1926	1929
NULL	NULL	827177	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	NULL	828975	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	NULL	828975	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
881035	NULL	881035	NULL	NULL	NULL	NULL	NULL	881035	NULL	881035
881035	NULL	881035	NULL	NULL	NULL	NULL	NULL	881035	NULL	881035
NULL	NULL	884596	NULL	NULL	NULL	884596	NULL	NULL	NULL	884596

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 09/21/2012 :  12:26:01  Show Profile  Reply with Quote
is the pivot columns static?

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

Go to Top of Page

WJHamel
Aged Yak Warrior

USA
644 Posts

Posted - 09/21/2012 :  12:27:17  Show Profile  Reply with Quote
yes. This is a new table created from the columns/rows i pivoted in another table.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 09/21/2012 :  12:45:00  Show Profile  Reply with Quote
then use like


....
WHERE
 (CASE WHEN [1930] IS NOT NULL THEN 1 END +
 CASE WHEN [1924] IS NOT NULL THEN 1 END +
 CASE WHEN [1927] IS NOT NULL THEN 1 END +
 CASE WHEN [1933] IS NOT NULL THEN 1 END +
 CASE WHEN [1934] IS NOT NULL THEN 1 END +
 CASE WHEN [1928] IS NOT NULL THEN 1 END +
 CASE WHEN [1931] IS NOT NULL THEN 1 END +
 CASE WHEN [1925] IS NOT NULL THEN 1 END +
 CASE WHEN [1932] IS NOT NULL THEN 1 END +
 CASE WHEN [1926] IS NOT NULL THEN 1 END +
 CASE WHEN [1929] IS NOT NULL THEN 1 END) >=3


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

Go to Top of Page

WJHamel
Aged Yak Warrior

USA
644 Posts

Posted - 09/21/2012 :  13:59:27  Show Profile  Reply with Quote
Isn't there an "else" value missing? Even though i can see rows with four columns populated, my resultset with the above is empty.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 09/21/2012 :  14:10:24  Show Profile  Reply with Quote
yep there should be ELSE 0 part added

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

Go to Top of Page

WJHamel
Aged Yak Warrior

USA
644 Posts

Posted - 09/21/2012 :  15:31:45  Show Profile  Reply with Quote
Excellent. Ok, so i used that to create a new table with all of the rows from the first table which had four or more columns with data in them. The columns are in order from left to right in ascending order. Somehow how, in the new table i created (douglasconversion.dbo.AbuseTypeDescFOURvalues) I need to keep the data in the column with the highest value. So if a row has data in columns 1924, 1925. 1930, 1934, i only want to keep the data in the 1934 column for that row.

What suggestions do you have for that?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 09/21/2012 :  16:01:18  Show Profile  Reply with Quote
you've write a logic based on case...when or use unpivot and max

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000