Solved

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

Posted on 2008-10-23
21
210 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 7
21 Comments
 
LVL 79

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 79

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 79

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
 
LVL 79

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 79

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 79

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 79

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

615 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