CREATE TABLE [Reference].[CRKAttr](
	[CRKID] [int] IDENTITY(1,1) NOT NULL,
	[Client] [varchar](100) NULL,
	[AttrOrder] [int] NULL,
	[AttrTable] [varchar](100) NULL,
	[Attr] [varchar](100) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING ON
GO
SET IDENTITY_INSERT [Reference].[CRKAttr] ON 

INSERT [Reference].[CRKAttr] ([CRKID], [Client], [AttrOrder], [AttrTable], [Attr]) VALUES (1, N'Brampton', 1, N'UPPOREP', N'pogucd')
INSERT [Reference].[CRKAttr] ([CRKID], [Client], [AttrOrder], [AttrTable], [Attr]) VALUES (2, N'Brampton', 2, N'UPPNREP', N'pougty')

SET IDENTITY_INSERT [Reference].[CRKAttr] OFF

CREATE TABLE [ETL].[Source](
	[SourceID] [int] IDENTITY(1,1) NOT NULL,
	[SourceName] [varchar](100) NOT NULL,
	[SourceDBName] [varchar](100) NOT NULL,
	[SourceServerName] [varchar](100) NOT NULL,
	[Process] [char](1) NOT NULL DEFAULT ('Y'),
	[ClientSourceID] [int] NULL,
	[ClosingPeriod] [varchar](6) NULL,
	[DMDBName] [varchar](100) NULL,
	[DMServer] [varchar](100) NULL,


INSERT [ETL].[Source] ([SourceID], [SourceName], [SourceDBName], [SourceServerName], [Process], [ClientSourceID], [ClosingPeriod], [DMDBName], [DMServer]) VALUES (3, N'Brampton', N'Cognos - Brampton (LIVE)', N'xxxx', N'Y', 3, N'200301', N'abcd', N'xxxx')

Hi All

I have the above table creation statement and have a problem.

Imagine that this client has 2 "AttrTables" but another client could have 3 or 4

I need to dynamically generate a SQL Statement which will do the selection for me with the associated join.

The output I need is:

Select distinct U1.pogucd, U2.pougty FROM [Cognos - Brampton (LIVE)].dbo.UPPOREP U1 CROSS JOIN [Cognos - Brampton (LIVE)].dbo.UPPNREP U2

I tried to create the statement but ended up getting frustrated with it, the reason is that the fields it picks up from differ between each client, i provided the easiest one here as it only has 2

Another client may have 3 tables and 3 different columns or even more

Does anyone know how to achieve what i'm looking to do? the alias names come from the first character of the AttrTable + Convert(varchar,AttrID) as the alias name for each column

Thanks and as always any help would be much appreciated :)

One last thing a nice ot have but not completely necessary - if theres a way to actually just do this selection instead of outputting the sql query i'd love to know both ways just for learning purposes

J

• Edited by James OHara 17 hours 40 minutes ago update

As I understand what you need is to dynamically build query based on the tables and then use it

ie like this

DECLARE @SQL varchar(max) ,@ColumnList varchar(1000),@TableList varchar(1000)


SELECT @TableList = STUFF((SELECT ' CROSS JOIN ' + QUOTENAME(SourceDBNAme) + '.dbo.' +  AttrTable + ' U' + CAST(ROW_NUMBER() OVER (ORDER BY ( SELECT 1)) AS varchar(5))
FROM ETL.Source
CROSS JOIN [Reference].[CRKAttr]
FOR XML PATH('')),1,12,''),
@ColumnList = STUFF((SELECT ',' +  ' U' + CAST(DENSE_RANK() OVER (ORDER BY AttrTable) AS varchar(5)) + '.' + Attr 
FROM ETL.Source
CROSS JOIN [Reference].[CRKAttr]
FOR XML PATH('')),1,1,'')

SELECT  @SQL= 'SELECT ' + @ColumnList + ' FROM ' + @TableList

PRINT(@SQL)
--EXEC (@SQL)

Once you find that returned query is right execute it by uncommenting EXEC

As I understand what you need is to dynamically build query based on the tables and then use it

ie like this

DECLARE @SQL varchar(max) ,@ColumnList varchar(1000),@TableList varchar(1000)


SELECT @TableList = STUFF((SELECT ' CROSS JOIN ' + QUOTENAME(SourceDBNAme) + '.dbo.' +  AttrTable + ' U' + CAST(ROW_NUMBER() OVER (ORDER BY ( SELECT 1)) AS varchar(5))
FROM ETL.Source
CROSS JOIN [Reference].[CRKAttr]
FOR XML PATH('')),1,12,''),
@ColumnList = STUFF((SELECT ',' +  ' U' + CAST(DENSE_RANK() OVER (ORDER BY AttrTable) AS varchar(5)) + '.' + Attr 
FROM ETL.Source
CROSS JOIN [Reference].[CRKAttr]
FOR XML PATH('')),1,1,'')

SELECT  @SQL= 'SELECT ' + @ColumnList + ' FROM ' + @TableList

PRINT(@SQL)
--EXEC (@SQL)

Once you find that returned query is right execute it by uncommenting EXEC

could you help me with understanding - when you say xmlpath 1,12 what does that switch actually do - maybe I can find a resource to read about this great function!

FOR XML PATH('') is a funny way to build a concatenated string. Here is a simpler example:

SELECT name + ', '
FROM   sys.objects
ORDER  BY name
FOR  XML PATH('')

Now this is some sort of XML, but we can recover is nvarchar(MAX) with some more XML mumbo-jumbo:

SELECT (SELECT name + ', '
        FROM   sys.objects
        ORDER  BY name
        FOR  XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')

Now, if you look at the end of the string, you see that there is a trailing comma, which we have to remove in some way. I prefer to save the list with the with the comma, and the strip the comma separately for greater clarity, but Visakh is doing it all at once with the stuff() funtion and the 1,12 are arguments to stuff().

Hi Both

Thanks for your help - i manaqged to finish the rest of the query by adjusting the behaviour slightly.

Just for others reading in the future, my final code block looked as follows:

DECLARE @SQL varchar(max) ,@ColumnList varchar(1000),@TableList varchar(1000),@ClientName varchar(1000),
 set @ClientName = 'Knapton'
 SELECT 

 @ColumnList = STUFF((SELECT ',' +left(attrtable,1) + CAST(DENSE_RANK() OVER (ORDER BY AttrTable) AS varchar(5)) + '.' + Attr 
FROM ave_control.ETL.Source
inner JOIN [Reference].[CRKAttr] on client = sourcename COLLATE DATABASE_DEFAULT
 where Client = @ClientName
 FOR XML PATH('')),1,1,''),


 
 @TableList = STUFF((SELECT distinct
 ' CROSS JOIN ' 
 + QUOTENAME(SourceDBNAme) 
 + '.dbo.' 
 + AttrTable 
 + ' ' 
 + left(attrtable,1) 
+ CAST(RANK()Over(Partition by Client order by Attrtable) AS varchar) COLLATE DATABASE_DEFAULT
 FROM Ave_Control.ETL.Source
 inner JOIN [Reference].[CRKAttr] on client = sourcename COLLATE DATABASE_DEFAULT
 where Client = @ClientName
 FOR XML PATH('')),1,12,'')
 
 SELECT @SQL= 'SELECT ' + @ColumnList + ' FROM ' + @TableList
 PRINT(@SQL)
 --EXEC (@SQL)
 

I adjusted the row_number to a rank and told it for the column list to select distinct. this fixed all eventualities possible in my database! thankyou for the help -

J