What if column names are different?
what @JeffModen suspected
Do you want all of the rows in a single return or do you want the rows from each table in a separate return.
A B C A. B C
1 2. 3. 11 22 14
67 34 23. 6 7. 45
Likee this
Understood. So, just as an example, if you had 3 tables that looked like this...
--===== Example Tables and data
DROP TABLE IF EXISTS dbo.Table1,dbo.Table2,dbo.Table3
;
SELECT *
INTO dbo.Table1
FROM (VALUES
( 1, 2, 4)
,(67,34,23)
,(10,11,12)
)v(A,B,C)
;
SELECT *
INTO dbo.Table2
FROM (VALUES
(11,22,14)
,( 6, 7,45)
)v(A,B,C)
;
SELECT *
INTO dbo.Table3
FROM (VALUES
(99,98,97)
,(96,95,94)
,(93,92,91)
,(90,89,88)
)v(A,B,C)
;
... that you wanted returns from and with the understanding that order is not guaranteed without an explicit ORDER BY, would you want the output to look like this?
A B C A B C A B C
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 2 4 11 22 14 99 98 14
67 34 23 6 7 45 96 95 45
10 11 12 NULL NULL NULL 93 92 NULL
NULL NULL NULL NULL NULL NULL 90 89 NULLAlso, you say the reason for wanting to do this is because of "business requirements". That's a bit of a cop out. We're all interested in just exactly what the "business requirements" are and what they're going to use the results for.
Please honor us with that information.
Sir , requirement is by passing the multiple table names at a time as a parameter I need to fetch the data
Example: I'm having ten tables, each table consists 10 fields, I need to select 3 tables and 4 fields in each table dynamically, then report has to display
Shifting requirements with every post.
Do you want the result of every table as seperate or union-ed
1 Like
@Vasu ,
Thank you but we're right back where we started. You've listed "what" you want to do... not "why" the end users want it done.
You've also not identified in the post just before the one you tried to answer is whether or not the format produced in my example (with the nulls if the tables are uneven) is acceptable. You've also not identified which rows (for example) of table 2 should align with which rows in table 1.
I also have to agree with @yosiasz that you do keep changing requirements and that even an expert archer is going to have a difficult time trying to cleanly hit the hole of a donut rolling down such a bumpy hill.
2 Likes
I am not shifting requirements, this is my actual requirement but in that I have asked you only about table selection. That's it..
6 fields
4 fields
Shifting requirements. Please provide sample source tables with sample data and sample expected results from those tables based on your stored procedure
1 Like
Ok. I'm looking at this. What are the 4 "fields" you need to look in each table "dynamically"?
Or, do you need to pass the field names in a variable like you are for the tables?
I used the below code for tables and columns selection..
It's working in sql but while linking this stored procedure in ssrs it is showing error.
Create procedure [dbo].[USP_REPORT]
@SchemaName VARCHAR(128),
@TableName VARCHAR(128),
@ColumnList VARCHAR(MAX)
AS
BEGIN
DECLARE @ColumnNames VARCHAR(MAX)
DECLARE @ColumnNamesVAR VARCHAR(MAX)
--drop ##Temp_Data Table
IF OBJECT_ID('tempdb..##Temp_Data') IS NOT NULL
DROP TABLE ##Temp_Data
--drop ##Temp_Data_Final Table
IF OBJECT_ID('tempdb..##Temp_Data_Final') IS NOT NULL
DROP TABLE ##Temp_Data_Final
--drop #Temp_Columns Table
IF OBJECT_ID('tempdb..#Temp_Columns') IS NOT NULL
DROP TABLE #Temp_Columns
Create table #ColumnList (Data NVARCHAR(MAX))
insert into #ColumnList values (@ColumnList)
--convert all column list to VARCHAR(1000) for unpivot
;with Cte_ColumnList as (
SELECT
'['+LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)')))+']' AS ColumnList
FROM
(
SELECT CAST('' + REPLACE(Data,',','')
- '' AS XML) AS x
FROM #ColumnList
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n))
,CTE_ColumnListVarchar as
(Select 'CAST('+ColumnList+' as VARCHAR(1000)) AS '+ColumnList AS ColumnListVAR,ColumnList from Cte_ColumnList)
SELECT @ColumnNamesVAR = COALESCE(@ColumnNamesVAR + ', ', '') + ColumnListVAR,
@ColumnNames = COALESCE(@ColumnNames + ', ', '') + ColumnList
FROM CTE_ColumnListVarchar
--Insert data into ##Temp_Data Table
DECLARE @SQL NVARCHAR(MAX)
DECLARE @TempTbleSQL NVARCHAR(MAX)
SET @TempTbleSQL='Select ROW_NUMBER()
OVER (order by (Select 1)) AS R,'+@ColumnNames +' into ##Temp_Data from ['+@SchemaName+'].['+@TableName+']'
--Print @TempTbleSQL
EXEC(@TempTbleSQL)
SET @SQL='
select
R,columnname,value into ##Temp_Data_Final from
(select R,'+@ColumnNamesVAR+' from ##Temp_Data )u
unpivot
(value for columnname in ('+@ColumnNames+'))v'
--Print @SQL
EXEC(@SQL)
Select * From ##Temp_Data_Final
END
--EXEC USP_REPORT 'dbo','table1','A,B,C'
This is very messy code and a maintenance nightmare. Despite all the recommendations from super experienced renowned sql gurus you still want to stubbornly do your own thing, which is your right. Yet now after spending multiple threads you want to disregard their recommendations and still want them to code review this?
Sir I did not get the actual output with all your codes..
So only I have sent this code.this code is working perfectly in SQL. But in report builder it is not working..
That's why I asked for help.
This will absolutely not work based on your original requirements of more than one table
Haa ss you are right, it will work for single table selection..
I'm just trying in report builder whether it is work or not.. but it showing error for single table selection also. What I have to do now.im trying from past 20 days onwards.
- provide clear non shape shifting requirements
- Listen and implement expert recommendations
you could have been done in 20 minutes
- provide clear non shape shifting requirements
- Listen and implement expert recommendations
Still waiting for #1. Help us help you.
I need to give multiple table names in selection