Solved

Need a stored procedure to put three rows of data in to one row

Posted on 2008-10-23
21
196 Views
Last Modified: 2012-05-05
hello,
i have a view (in snippet below)....that concatenate's 23 subheads and puts them in to Sub1, Sub2 and Sub3.  The user can select from three separate drop down lists, so my view is always going to return three rows of data based on user selection.  i have attached a sample of how the data returns....

How can i write a stored procedure to put the values in to one row and get them out?  i need row 1 sub1, row2 sub2, and row3 sub3 all put in to one row.....
please help....newbie.
SELECT    p.Display_Title, p.Subhead_1, p.Subhead_2, p.Subhead_3, p.Subhead_4, p.Subhead_5, p.Subhead_6, p.Subhead_7, p.Subhead_8, p.Subhead_9, 

          p.Subhead_10, p.Subhead_11, p.Subhead_12, p.Subhead_13, p.Subhead_14, p.Subhead_15, p.Subhead_16, p.Subhead_17, p.Subhead_18, 

          p.Subhead_19, p.Subhead_20, p.Subhead_21, p.Subhead_22, p.Subhead_23, p.Copyblock_1, p.Copyblock_2, p.Copyblock_3, p.Copyblock_4, 

          p.Copyblock_5, p.Copyblock_6, p.Copyblock_7, p.Copyblock_8, p.Copyblock_9, p.Copyblock_10, p.Copyblock_11, p.Copyblock_12, p.Copyblock_13, 

          p.Copyblock_14, p.Copyblock_15, p.Copyblock_16, p.Copyblock_17, p.Copyblock_18, p.Copyblock_19, p.Copyblock_20, p.Copyblock_21, 

          p.Copyblock_22, p.Copyblock_23, p.Copyblock_24, p.Copyblock_25, p.Copyblock_26, p.Copyblock_27, p.Copyblock_28, p.Copyblock_29, 

          p.Copyblock_30, p.Copyblock_31, p.Copyblock_32, p.Copyblock_33, p.Copyblock_34, p.Copyblock_35, p.Copyblock_36, p.Copyblock_37, 

          p.Copyblock_38, p.Copyblock_39, p.Copyblock_40, p.Copyblock_41, p.Copyblock_42, p.Copyblock_43, p.Copyblock_44, p.Copyblock_45, 

          p.Copyblock_46, p.Copyblock_47, p.Copyblock_48, p.Copyblock_49, p.Copyblock_50, p.Copyblock_51, p.Copyblock_52, p.Copyblock_53, 

          p.Copyblock_54, p.Copyblock_55, p.Copyblock_56, p.Copyblock_57, p.Copyblock_58, p.Copyblock_59, p.Copyblock_60, p.Copyblock_61, 

          p.Copyblock_62, p.Copyblock_63, p.Copyblock_64, p.Copyblock_65, p.Copyblock_66, p.Copyblock_67, p.Copyblock_68, p.Copyblock_69, 

          p.Copyblock_70, p.Copyblock_71, p.Copyblock_72, p.Copyblock_73, p.Copyblock_74, p.Copyblock_75, p.Copyblock_76, p.Copyblock_77, 

          p.Copyblock_78, p.Copyblock_79, p.Copyblock_80, p.Copyblock_81, p.Copyblock_82, p.Copyblock_83, p.Copyblock_84, p.Copyblock_85, 

          p.Copyblock_86, p.Copyblock_87, p.Copyblock_88, p.Copyblock_89, p.Copyblock_90, p.Copyblock_91, p.Copyblock_92, p.Copyblock_93, 

          p.Copyblock_94, p.Copyblock_95, p.Copyblock_96, p.Copyblock_97, p.Copyblock_98, p.Copyblock_99, p.Copyblock_100, p.Copyblock_101, 

          p.Copyblock_102, p.Copyblock_103, p.Copyblock_104, p.Copyblock_105, p.Copyblock_106, p.Copyblock_107, p.Copyblock_108, p.Copyblock_109, 

          p.Copyblock_110, p.Copyblock_111, p.Copyblock_112, p.Copyblock_113, p.Copyblock_114, p.Copyblock_115, p.Copyblock_116, p.Copyblock_117, 

          p.Copyblock_118, p.Copyblock_119, p.Copyblock_120, p.Copyblock_121, p.Copyblock_122, p.Copyblock_123, 

          p.Subhead_1 + p.Subhead_2 + p.Subhead_3 + p.Subhead_4 + p.Subhead_5 + p.Subhead_6 + p.Subhead_7 + p.Subhead_8 + p.Subhead_9 + p.Subhead_10

                      + p.Subhead_11 + p.Subhead_12 + p.Subhead_13 + p.Subhead_14 + p.Subhead_15 + p.Subhead_16 + p.Subhead_17 + p.Subhead_18 

                      + p.Subhead_19 + p.Subhead_20 + p.Subhead_21 + p.Subhead_22 + p.Subhead_23 AS Sub1, 

          p.Subhead_1 + p.Subhead_2 + p.Subhead_3 + p.Subhead_4 + p.Subhead_5 + p.Subhead_6 + p.Subhead_7 + p.Subhead_8 + p.Subhead_9 + p.Subhead_10

                      + p.Subhead_11 + p.Subhead_12 + p.Subhead_13 + p.Subhead_14 + p.Subhead_15 + p.Subhead_16 + p.Subhead_17 + p.Subhead_18 

                      + p.Subhead_19 + p.Subhead_20 + p.Subhead_21 + p.Subhead_22 + p.Subhead_23 AS Sub2, 

          p.Subhead_1 + p.Subhead_2 + p.Subhead_3 + p.Subhead_4 + p.Subhead_5 + p.Subhead_6 + p.Subhead_7 + p.Subhead_8 + p.Subhead_9 + p.Subhead_10

                      + p.Subhead_11 + p.Subhead_12 + p.Subhead_13 + p.Subhead_14 + p.Subhead_15 + p.Subhead_16 + p.Subhead_17 + p.Subhead_18 

                      + p.Subhead_19 + p.Subhead_20 + p.Subhead_21 + p.Subhead_22 + p.Subhead_23 AS Sub3

 

FROM      dbo.TO_Product AS p

 

WHERE    (ISNULL(p.Display_Title, '') > '')

Open in new window

0
Comment
Question by:tarashea
  • 13
  • 7
21 Comments
 
LVL 76

Expert Comment

by:arnold
ID: 22786689
Can you post some sample data from the view?
can you also post the queries that are generated based on the three drop down selections?


Are the three returned rows valid answers or can be they eliminated/excluded by including a check on whether a particular column is NULL?

0
 

Author Comment

by:tarashea
ID: 22787465
sorry, i thought i attached it.  

they are all valid rows of data....i just need the first returned row's Sub1, the second returned row's Sub2, and the third returned row's Sub3 put in to one row.
data-returned.xls
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22788890
To get the first non null value you could use COALESCE

Coalesce(subhead_1,subhead_2,subhead_3,....) as row1

but for the second and third non null, you are better to write a function or construct a big big big case statement
0
 

Author Comment

by:tarashea
ID: 22788948
i already have my data coming in to a view and putting the value in Sub1, Sub2 and Sub3.  

when the user selects from the three drop down lists, the three choices query the view.  so my select on the view would be something like this:

select * from TO_Product_View where (Display_Title like 'Confront-How it Works') OR (Display_Title like 'Confront-Weeds Controlled') OR (Display_Title like 'Dithane-Rainshield Technology')

that returns me three rows of data for each one, and each one has Sub1, Sub2 and Sub3 that are all the same (you can see in the sample data file i attached).  i need to combine those three rows in to one row where i would have:

Confront-How it Works = Sub1
Confront-Weeds Controlled = Sub2
Dithane-Rainshield Technology = Sub3



0
 

Author Comment

by:tarashea
ID: 22788973
to add to that....

instead of my app calling the view statement above, i need to use a stored procedure that creates variables that query the view and bring me back the one row of combined data.
0
 
LVL 76

Expert Comment

by:arnold
ID: 22790034
Why perform the work twice?  Once in maintaining a view and then repeatedly per query?  You should get the view better organized.  It looks like you took many  columns from  dbo.TO_Product created the identical structure in a vew TO_Product_view that change and added additional columns.

The data sample you provided does not clear the issue up.

Are there multiple rows with the Display_Title containing 'Confront -How it Works'?  Or are you dealing with the 'OR' statement that returns three rows one for each Display_title?

Another issue, your query uses 'like' for comparison while you are not including any wildcards ('%') why not use = instead?

0
 

Author Comment

by:tarashea
ID: 22790268
these three variables need to map to a template in a pageflex app.  The TO_Product_View is necessary because each variable could be used in any spot.  So the view makes the same subhead available to Sub1, Sub2 and Sub3 by concatenating all the possible subheads from TO_Product, per display_title.

The data I provided was just to show how the data comes back from the view.  

The user selects three separate display_titles to dynamically fill three spots on the template.  

Right now those three rows return and i get something like this new file i just attached.  in the file, i added how i NEED the data to come back to me.

pageflex will only accept one row of data to map to the template variables.  So I somehow need to take the three I am getting back, get their associated subhead, and pull it back in one row.  If i can do it in the view with some extra coding, that's fine.  pageflex suggested i do it by using a stored procedure to create the variables that will map back to my template.

and yes, i can use the = instead of like....i was just following the protocol pageflex uses.


data.xls
0
 
LVL 76

Expert Comment

by:arnold
ID: 22791805
While I can see what you want, the grouping is in a sense arbitrary.
I.e. the order in which the OR statements are group could affect the order in which the rows are returned.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22452516.html

i.e.
you have to declare all variables:

Below is the main portion of the stored procedure.  Not sure whether the return is the correct mechanism or using a select.
select @sub1=sub1,@primary_key=primary_key from TO_Product_View where (Display_Title like 'Confront-How it Works')

select @sub2=sub2,@primary_key2=primary_key2 from TO_Product_View where (Display_Title like 'Confront-Weeds Controlled')

select @sub3=sub3,@primary_key_cross_sell=primary_key_cross_sell from TO_Product_View where (Display_Title like 'Dithane-Rainshield Technology')
 

select @primary_key as primary_key, @primary_key2 as primary_key2, @primary_key_cross_sell as primary_key_cross_sell, @sub1 as sub1, @sub2 as sub2, @sub3 as sub3
 

return (@primary_key, @primary_key2, @primary_key_cross_sell, @sub1,@sub2,@sub3)

Open in new window

0
 

Author Comment

by:tarashea
ID: 22796185
OK. this is the stored procedure i am trying to write, and i am getting syntax errors....on these lines

Msg 102, Level 15, State 1, Procedure getViewData, Line 6
Incorrect syntax near ')'.

on this line:  CREATE PROCEDURE [dbo].[getViewData]()

and

Msg 102, Level 15, State 1, Procedure getViewData, Line 35
Incorrect syntax near ','.

on this line: return (@primary_key, @primary_key2, @primary_key_cross_sell, @sub1,@sub2,@sub3)

am i missing something?  it looks correct to me.
CREATE PROCEDURE [dbo].[getViewData]()
 

AS

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	SET NOCOUNT ON;
 

	--these hold what's in the select statement

	DECLARE @sub1 varchar(5000)

	DECLARE @sub2 varchar(5000)

	DECLARE @sub3 varchar(5000)

	DECLARE @primary_key varchar(5000)

	DECLARE @primary_key2 varchar(5000)

	DECLARE @primary_key_cross_sell varchar(5000)
 

    -- Insert statements for procedure here

	select @sub1=sub1,@primary_key=primary_key from TO_Product_View where (Display_Title like 'Confront-How it Works')

    select @sub2=sub2,@primary_key2=primary_key2 from TO_Product_View where (Display_Title like 'Confront-Weeds Controlled')

    select @sub3=sub3,@primary_key_cross_sell=primary_key_cross_sell from TO_Product_View where (Display_Title like 'Dithane-Rainshield Technology')

 

    select @primary_key as primary_key, @primary_key2 as primary_key2, @primary_key_cross_sell as primary_key_cross_sell, @sub1 as sub1, @sub2 as sub2, @sub3 as sub3

 

    return (@primary_key, @primary_key2, @primary_key_cross_sell, @sub1,@sub2,@sub3)

END

GO

Open in new window

0
 

Author Comment

by:tarashea
ID: 22796273
should this stored procedure will take in the three display_title arguments?

like this:

CREATE PROCEDURE [dbo].[getViewData](
      @PRIME_KEY varchar(5000),
      @PRIME_KEY_2(5000),
      @PRIME_KEY_CROSS_SELL(5000)
)

otherwise how will it know what to query the view on?

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 76

Expert Comment

by:arnold
ID: 22796304
return might only return 1 or 0.
remove the () from the create line.
CREATE PROCEDURE [dbo].[getViewData]

AS

BEGIN

        -- SET NOCOUNT ON added to prevent extra result sets from

        -- interfering with SELECT statements.

        SET NOCOUNT ON;

 

        --these hold what's in the select statement

        DECLARE @sub1 varchar(5000)

        DECLARE @sub2 varchar(5000)

        DECLARE @sub3 varchar(5000)

        DECLARE @primary_key varchar(5000)

        DECLARE @primary_key2 varchar(5000)

        DECLARE @primary_key_cross_sell varchar(5000)

 

    -- Insert statements for procedure here

        select @sub1=sub1,@primary_key=primary_key from TO_Product_View where (Display_Title like 'Confront-How it Works')

    select @sub2=sub2,@primary_key2=primary_key2 from TO_Product_View where (Display_Title like 'Confront-Weeds Controlled')

    select @sub3=sub3,@primary_key_cross_sell=primary_key_cross_sell from TO_Product_View where (Display_Title like 'Dithane-Rainshield Technology')

 

    select @primary_key as primary_key, @primary_key2 as primary_key2, @primary_key_cross_sell as primary_key_cross_sell, @sub1 as sub1, @sub2 as sub2, @sub3 as sub3

 

END

GO

Open in new window

0
 
LVL 76

Expert Comment

by:arnold
ID: 22796413
First thing first, to make sure the stored procedure works as needed in terms of the response.

Then you can add the parameters, but I do not think you need to enclose them in parenthesis.
If the above works,  
CREATE PROCEDURE [dbo].[getViewData]

        @data_sub1 VARCHAR(5000),

        @data_sub2 VARCHAR(5000),

        @data_sub3 VARCHAR(5000)

AS

BEGIN

        -- SET NOCOUNT ON added to prevent extra result sets from

        -- interfering with SELECT statements.

        SET NOCOUNT ON;

 

        --these hold what's in the select statement

        DECLARE @sub1 varchar(5000)

        DECLARE @sub2 varchar(5000)

        DECLARE @sub3 varchar(5000)

        DECLARE @primary_key varchar(5000)

        DECLARE @primary_key2 varchar(5000)

        DECLARE @primary_key_cross_sell varchar(5000)

 

    -- Insert statements for procedure here

        select @sub1=sub1,@primary_key=primary_key from TO_Product_View where (Display_Title like @data_sub1)

    select @sub2=sub2,@primary_key2=primary_key2 from TO_Product_View where (Display_Title like @data_sub2)

    select @sub3=sub3,@primary_key_cross_sell=primary_key_cross_sell from TO_Product_View where (Display_Title like @data_sub3)

 

    select @primary_key as primary_key, @primary_key2 as primary_key2, @primary_key_cross_sell as primary_key_cross_sell, @sub1 as sub1, @sub2 as sub2, @sub3 as sub3

 

END

GO

Open in new window

0
 

Author Comment

by:tarashea
ID: 22796425
OK. i entered what i just tried to run below....

Here are my errors now:

Msg 207, Level 16, State 1, Procedure getViewData, Line 23
Invalid column name 'primary_key'.
Msg 207, Level 16, State 1, Procedure getViewData, Line 24
Invalid column name 'primary_key2'.
Msg 207, Level 16, State 1, Procedure getViewData, Line 25
Invalid column name 'primary_key_cross_sell'.

On these three lines:

select @sub1=sub1,@primary_key=primary_key from TO_Product_View where (Display_Title like 'Confront-How it Works')
    select @sub2=sub2,@primary_key2=primary_key2 from TO_Product_View where (Display_Title like 'Confront-Weeds Controlled')
    select @sub3=sub3,@primary_key_cross_sell=primary_key_cross_sell from TO_Product_View where (Display_Title like 'Dithane-Rainshield Technology')

Should this part (@primary_key=primary_key) be the display_title?  

And should this part  (Display_Title like 'Confront-How it Works') be a variable that is passed in from the user selection instead of the actual product name?

CREATE PROCEDURE [dbo].[getViewData]

AS

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	SET NOCOUNT ON;
 

	--  declare my variables

	DECLARE @sub1 varchar(5000)

	DECLARE @sub2 varchar(5000)

	DECLARE @sub3 varchar(5000)

	DECLARE @primary_key varchar(5000)

	DECLARE @primary_key2 varchar(5000)

	DECLARE @primary_key_cross_sell varchar(5000)
 

    -- Insert statements for procedure here

	select @sub1=sub1,@primary_key=primary_key from TO_Product_View where (Display_Title like 'Confront-How it Works')

    select @sub2=sub2,@primary_key2=primary_key2 from TO_Product_View where (Display_Title like 'Confront-Weeds Controlled')

    select @sub3=sub3,@primary_key_cross_sell=primary_key_cross_sell from TO_Product_View where (Display_Title like 'Dithane-Rainshield Technology')

 

    select @primary_key as primary_key, @primary_key2 as primary_key2, @primary_key_cross_sell as primary_key_cross_sell, @sub1 as sub1, @sub2 as sub2, @sub3 as sub3

 

    return 

END

GO

Open in new window

0
 

Author Comment

by:tarashea
ID: 22796450
OK, i will leave the parameters out for now....and focus on the functionality first.  good call.
0
 

Author Comment

by:tarashea
ID: 22796564
PRIME_KEY, PRIME_KEY_2 and PRIME_KEY_CROSS_SELL are the variables from the pageflex template...they all just the display_titles that the user picks.  They aren't in the view.
0
 
LVL 76

Expert Comment

by:arnold
ID: 22796586
I have no idea what columns you have in the TO_Product_View.
You need to replace the column referenced in the select @variable=column from table/view to match the column name in the table/view.
0
 

Author Comment

by:tarashea
ID: 22796628
OK, i ran what i pasted below.....and i think it's working!!!!

 this is what it returned:

primary_key = Confront-How it Works      
primary_key_2 = Confront-Weeds Controlled      
primary_key_cross_sell = Dithane-Rainshield Technology      
sub1 = How It Works      
sub2 = Weeds Controlled      
sub3 = Rainshield® technology

so now i just need to try it passing the parameters, right????
ALTER PROCEDURE [dbo].[getViewData]

AS

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	SET NOCOUNT ON;
 

	--  declare my variables

	DECLARE @sub1 varchar(5000)

	DECLARE @sub2 varchar(5000)

	DECLARE @sub3 varchar(5000)

	DECLARE @display_title varchar(5000)

	DECLARE @primary_key varchar(5000)

	DECLARE @primary_key2 varchar(5000)

	DECLARE @primary_key_cross_sell varchar(5000)
 

    -- Insert statements for procedure here

	select @sub1=sub1,@primary_key=display_title from TO_Product_View where (Display_Title like 'Confront-How it Works')

    select @sub2=sub2,@primary_key2=display_title from TO_Product_View where (Display_Title like 'Confront-Weeds Controlled')

    select @sub3=sub3,@primary_key_cross_sell=display_title from TO_Product_View where (Display_Title like 'Dithane-Rainshield Technology')

 

    select @primary_key as primary_key, @primary_key2 as primary_key2, @primary_key_cross_sell as primary_key_cross_sell, @sub1 as sub1, @sub2 as sub2, @sub3 as sub3

 

    return 

END

Open in new window

0
 

Author Comment

by:tarashea
ID: 22796768
also, since i'm a newbie....when i replace the current select in my pageflex app, it was this:

select * from TO_Product_View where (Display_Title like '<<PRIME_KEY>>') OR (Display_Title like '<<PRIME_KEY_2>>') OR (Display_Title like '<<PRIME_KEY_CROSS_SELL>>')

what is the syntax i will use to call the stored procedure with those three parameters?

EXEC getViewData (and then how do write parameters here?)
0
 
LVL 76

Accepted Solution

by:
arnold earned 500 total points
ID: 22796892
EXEC getViewData '<<PRIME_KEY>>','<<PRIME_KEY_2>>','<<PRIME_KEY_CROSS_SELL>>')


ALTER PROCEDURE [dbo].[getViewData]

        @data_sub1 VARCHAR(5000),

        @data_sub2 VARCHAR(5000),

        @data_sub3 VARCHAR(5000)

AS

BEGIN

        -- SET NOCOUNT ON added to prevent extra result sets from

        -- interfering with SELECT statements.

        SET NOCOUNT ON;

 

        --  declare my variables

        DECLARE @sub1 varchar(5000)

        DECLARE @sub2 varchar(5000)

        DECLARE @sub3 varchar(5000)

        DECLARE @display_title varchar(5000)

        DECLARE @primary_key varchar(5000)

        DECLARE @primary_key2 varchar(5000)

        DECLARE @primary_key_cross_sell varchar(5000)

 

    -- Insert statements for procedure here

        select @sub1=sub1,@primary_key=display_title from TO_Product_View where (Display_Title like @data_sub1)

    select @sub2=sub2,@primary_key2=display_title from TO_Product_View where (Display_Title like @data_sub2)

    select @sub3=sub3,@primary_key_cross_sell=display_title from TO_Product_View where (Display_Title like @data_sub3)

 

    select @primary_key as primary_key, @primary_key2 as primary_key2, @primary_key_cross_sell as primary_key_cross_sell, @sub1 as sub1, @sub2 as sub2, @sub3 as sub3

 

    return 

END

Open in new window

0
 

Author Comment

by:tarashea
ID: 22796958
OK, perfect...i am testing it out now!  stay tuned....
0
 

Author Closing Comment

by:tarashea
ID: 31509203
you ROCK!  thank you so much!
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now