SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Put first line as column name


Put first line as column name

Author
Message
ulisseslourenco
ulisseslourenco
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 298
Hi people,

I am creating a function to be used in a specific application that a user can insert any kind of information into a table.
This code below:



IF NOT EXISTS(SELECT * FROM SYS.tables WHERE name = 'TB_HEADER')
CREATE TABLE TB_HEADER
(ID_HEADER INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
CODIGO VARCHAR(50) UNIQUE,
COL1_H VARCHAR(100),
COL2_H VARCHAR(100),
COL3_H VARCHAR(100),
COL4_H VARCHAR(100),
COL5_H VARCHAR(100),
COL6_H VARCHAR(100),
COL7_H VARCHAR(100),
COL8_H VARCHAR(100),
COL9_H VARCHAR(100),
COL10_H VARCHAR(100)
)
GO

IF NOT EXISTS(SELECT * FROM SYS.tables WHERE name = 'TB_DADOS')
CREATE TABLE TB_DADOS
(ID_DADOS INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
ID_HEADER INT,
COL1_D VARCHAR(100),
COL2_D VARCHAR(100),
COL3_D VARCHAR(100),
COL4_D VARCHAR(100),
COL5_D VARCHAR(100),
COL6_D VARCHAR(100),
COL7_D VARCHAR(100),
COL8_D VARCHAR(100),
COL9_D VARCHAR(100),
COL10_D VARCHAR(100)
)
GO

INSERT INTO TB_HEADER
SELECT
CODIGO = 'CARRO_01',
COL1_H = 'MODELO_CARRO',
COL2_H = 'ANO',
COL3_H = 'DATA_COMPRA',
COL4_H = 'COR',
COL5_H = 'PLACA',
COL6_H = 'COL6',
COL7_H = 'COL7',
COL8_H = 'COL8',
COL9_H = 'COL9',
COL10_H = 'COL10'
GO

INSERT INTO TB_HEADER
SELECT
CODIGO = 'IMOVEL_01',
COL1_H = 'TIPO_DE_IMOVEL',
COL2_H = 'LOCALIZAÇÃO',
COL3_H = 'VALOR_ENTRADA',
COL4_H = 'QTD_PARCELA',
COL5_H = 'VALOR_PARCELA',
COL6_H = 'MOEDA',
COL7_H = 'COL7',
COL8_H = 'COL8',
COL9_H = 'COL9',
COL10_H = 'COL10'
GO

INSERT INTO TB_DADOS
SELECT
ID_HEADER = ID_HEADER,
COL1_D = 'PALIO',
COL2_D = '2012',
COL3_D = '05/02/2013',
COL4_D = 'VERMELHO',
COL5_D = 'XPT 1515',
COL6_D = 'COL6',
COL7_D = 'COL7',
COL8_D = 'COL8',
COL9_D = 'COL9',
COL10_D = 'COL10'
FROM TB_HEADER
WHERE CODIGO = 'CARRO_01'
GO

INSERT INTO TB_DADOS
SELECT
ID_HEADER = ID_HEADER,
COL1_D = 'CRV',
COL2_D = '2011',
COL3_D = '23/06/2012',
COL4_D = 'PRATA',
COL5_D = 'ABC 2020',
COL6_D = '',
COL7_D = '',
COL8_D = '',
COL9_D = '',
COL10_D = ''
FROM TB_HEADER
WHERE CODIGO = 'CARRO_01'
GO

INSERT INTO TB_DADOS
SELECT
ID_HEADER = ID_HEADER,
COL1_D = 'APARTAMENTO',
COL2_D = 'BARRA DA TIJUCA',
COL3_D = '200.000,00',
COL4_D = '48',
COL5_D = '8.000,00',
COL6_D = 'R$',
COL7_D = '',
COL8_D = '',
COL9_D = '',
COL10_D = ''
FROM TB_HEADER
WHERE CODIGO = 'IMOVEL_01'
GO

CREATE FUNCTION DBO.UF_DADOS (@CODIGO VARCHAR(50))
RETURNS TABLE
AS
RETURN

SELECT COL1_H, COL2_H, COL3_H, COL4_H, COL5_H, COL6_H, COL7_H, COL8_H, COL9_H, COL10_H
FROM TB_HEADER
WHERE CODIGO = @CODIGO

UNION ALL

SELECT COL1_D, COL2_D, COL3_D, COL4_D, COL5_D, COL6_D, COL7_D, COL8_D, COL9_D, COL10_D
FROM TB_DADOS
INNER JOIN TB_HEADER ON TB_HEADER.ID_HEADER = TB_DADOS.ID_HEADER
WHERE CODIGO = @CODIGO
GO




Although big query, that is a query function creation. The problem is: How can I return first lines from "tb_Header" to be column name?, I thought in "sp_rename", but I afraid it can be slow in a big table. I'd like to emphasize that columns are always dynamic.

Part of code below



CREATE FUNCTION DBO.UF_DADOS (@CODIGO VARCHAR(50))
RETURNS TABLE
AS
RETURN

SELECT COL1_H, COL2_H, COL3_H, COL4_H, COL5_H, COL6_H, COL7_H, COL8_H, COL9_H, COL10_H
FROM TB_HEADER
WHERE CODIGO = @CODIGO

UNION ALL

SELECT COL1_D, COL2_D, COL3_D, COL4_D, COL5_D, COL6_D, COL7_D, COL8_D, COL9_D, COL10_D
FROM TB_DADOS
INNER JOIN TB_HEADER ON TB_HEADER.ID_HEADER = TB_DADOS.ID_HEADER
WHERE CODIGO = @CODIGO
GO




grateful for your attention
Erin Ramsay
Erin Ramsay
SSC Eights!
SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)

Group: General Forum Members
Points: 815 Visits: 1099
Something like this might get you started:

declare @strSQL as varchar(max)

set @strsql = (select 'select COL1_D as ' + col1_h +
', COL2_D as ' + col2_h +
', COL3_D as ' + col3_h +
', COL4_D as ' + col4_h +
', COL5_D as ' + col5_h +
', COL6_D as ' + col6_h +
', COL7_D as ' + col7_h +
', COL8_D as ' + col8_h +
', COL9_D as ' + col9_h +
', COL10_D as ' + col10_h +
' from tb_dados ' +
'inner join tb_header on tb_dados.ID_header = tb_header.ID_Header ' +
'where tb_header.codigo = ''CARRO_01''' from tb_header where codigo = 'CARRO_01'
)

exec (@strSQL)


ulisseslourenco
ulisseslourenco
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 298
Thanks for respond me.

I have a question: Is allowed use exec statement into a function? I ask that because I have to return a select into a application calling one

SELECT * FROM DBO.UF_DADOS ('CARRO_01')
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25831 Visits: 17509
ulisseslourenco (7/2/2013)
Thanks for respond me.

I have a question: Is allowed use exec statement into a function? I ask that because I have to return a select into a application calling one

SELECT * FROM DBO.UF_DADOS ('CARRO_01')


No in SQL a function cannot use any dynamic sql.

I have to ask, why have you created such a generic piece of code? This is like "one function to rule them all". You would never consider doing this in a programming language yet we see it so frequently in the database world. The performance of things like this degrade as more and more data enters the system and there is nothing you can do to make it faster because everything is so generic.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ulisseslourenco
ulisseslourenco
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 298
Sean Lange (7/2/2013)
ulisseslourenco (7/2/2013)
Thanks for respond me.

I have a question: Is allowed use exec statement into a function? I ask that because I have to return a select into a application calling one

SELECT * FROM DBO.UF_DADOS ('CARRO_01')


No in SQL a function cannot use any dynamic sql.

I have to ask, why have you created such a generic piece of code? This is like "one function to rule them all". You would never consider doing this in a programming language yet we see it so frequently in the database world. The performance of things like this degrade as more and more data enters the system and there is nothing you can do to make it faster because everything is so generic.


The other functions work as well, the problem occurs because the programmer can insert a several of information into that tables and union statement into function could replace header table lines in a column name in function return. As I wrote in initial post I thought use sp_rename, however I afraid decreasing performance.

In relation, use other language, I don't know if it's possible, because this process will work into an application that runs sql only.

best regards
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25831 Visits: 17509
ulisseslourenco (7/2/2013)
Sean Lange (7/2/2013)
ulisseslourenco (7/2/2013)
Thanks for respond me.

I have a question: Is allowed use exec statement into a function? I ask that because I have to return a select into a application calling one

SELECT * FROM DBO.UF_DADOS ('CARRO_01')


No in SQL a function cannot use any dynamic sql.

I have to ask, why have you created such a generic piece of code? This is like "one function to rule them all". You would never consider doing this in a programming language yet we see it so frequently in the database world. The performance of things like this degrade as more and more data enters the system and there is nothing you can do to make it faster because everything is so generic.


The other functions work as well, the problem occurs because the programmer can insert a several of information into that tables and union statement into function could replace header table lines in a column name in function return. As I wrote in initial post I thought use sp_rename, however I afraid decreasing performance.

In relation, use other language, I don't know if it's possible, because this process will work into an application that runs sql only.

best regards


Not really sure what you mean by the other functions work as well. You cannot execute dynamic sql in a function in sql server.

I think you missed my point about making this so generic. In a programming language you would not create a method that does everything. It seems that is what you are trying to do here. You are creating a procedure/function that can do anything with any data of any type.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ulisseslourenco
ulisseslourenco
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 298
Yes, you are right about dynamic queries into functions. When I wrote about "the other functions work as well" I said the other functions are inserting information into a table correctly , but I have a function that joins two tables to show a result

for example:


COL1 | COL2 |COL3 | COL4 |
CAR MODEL| Year | Purchase data | color |
VW Bettle | 2009 | 2009-01-01 | red |
Ford Ka | 2013 | 2013 -05-16 | silver |


Based on the function



CREATE FUNCTION DBO.UF_DADOS (@CODIGO VARCHAR(50))
RETURNS TABLE
AS
RETURN

SELECT COL1_H, COL2_H, COL3_H, COL4_H, COL5_H, COL6_H, COL7_H, COL8_H, COL9_H, COL10_H
FROM TB_HEADER
WHERE CODIGO = @CODIGO

UNION ALL

SELECT COL1_D, COL2_D, COL3_D, COL4_D, COL5_D, COL6_D, COL7_D, COL8_D, COL9_D, COL10_D
FROM TB_DADOS
INNER JOIN TB_HEADER ON TB_HEADER.ID_HEADER = TB_DADOS.ID_HEADER
WHERE CODIGO = @CODIGO




I should have a result like a this:


CAR MODEL| Year | Purchase data | color |
VW Bettle | 2009 | 2009-01-01 | red |
Ford Ka | 2013 | 2013 -05-16 | silver |

Replacing Col1, Col2, Col3 and Col4 for CAR MODEL,Year,Purchase,data and color. Actually I haven't gotten yet.

Best Regards
ulisseslourenco
ulisseslourenco
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 298
Hi guys,

Could somebody help me? This is a bit urgent



Best regards
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25831 Visits: 17509
ulisseslourenco (7/8/2013)
Hi guys,

Could somebody help me? This is a bit urgent



Best regards


In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25831 Visits: 17509
Thanks to Erin for pointing out that you had posted ddl and sample data in your first post. Blush

Now your most recent post does not match that new sample data that you mentioned. Please help me understand what you are trying to do here.

I will go back to my original comment of why are you making everything so overly generic? You have violated so many normalization rules in your ddl I don't know where to begin. You have mixed datatypes in a single column, you have a different number of columns per row. Data integrity, validation, etc etc is impossible here. You can't do any kind of calculations or date conditions because of all the mixed data.

I will help you figure out the short term fix but honestly you need a long term solution.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search