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
 Order data , extra data

Author  Topic 

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-09-21 : 11:09:16
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

52326 Posts

Posted - 2012-09-21 : 12:26:01
is the pivot columns static?

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

Go to Top of Page

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-09-21 : 12:27:17
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

52326 Posts

Posted - 2012-09-21 : 12:45:00
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

651 Posts

Posted - 2012-09-21 : 13:59:27
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

52326 Posts

Posted - 2012-09-21 : 14:10:24
yep there should be ELSE 0 part added

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

Go to Top of Page

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-09-21 : 15:31:45
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

52326 Posts

Posted - 2012-09-21 : 16:01:18
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
   

- Advertisement -