January 27, 2015 at 11:05 am
I've got a web app that needs to dynamically pull/store information for reporting purposes.
Let's say I have a ReportDefinition and a ReportColumns table like so:
/* Create Report Columns table */
CREATE TABLE #ReportColumns(
[ReportColumnID] [int] IDENTITY(1,1) NOT NULL,
[ReportColumnValue] [varchar](50) NOT NULL, ) ON [PRIMARY]
INSERT INTO #ReportColumns (ReportColumnValue)
VALUES ('Column 1'),('Column 2'),('Column 3'), ('Column 4'), ('Column 5')
/* Create Report Definition Table */
CREATE TABLE #ReportDefinition (
[ReportID] [int] IDENTITY(1,1) NOT NULL,
[ReportColumns] [varchar](500) NULL,
)
INSERT INTO #ReportDefinition (ReportColumns)
VALUES ('1,3,5')Which looks something like this:
ReportID ReportColumns
----------- ------------
1 1,3,5
ReportColumnID ReportColumnValue
-------------- --------------------------
1 Column 1
2 Column 2
3 Column 3
4 Column 4
5 Column 5
I want to write a dynamic query that will pull out the actually column names (Column1, Column2...etc) and build a SELECT statement using the columns names from the ReportColumns table, rather than the numeric values that were stored.
So basically the output of the dynamic SQL if you were to print it out would look like:
SELECT Column1, Column3, Column5 FROM MyTable WHERE This = That
Any ideas how to best accomplish this?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 27, 2015 at 11:32 am
What stops you from normalizing the ReportDefinition table?
January 27, 2015 at 11:42 am
Not sure what you mean there Luis. There could be 300 different columns in the ReportColumns table and I wasn't sure how best to link them all together
I didn't want to add 300 possible columns to the report definitions table...
Here's what i came up with so far but thought maybe there'd be a better way SELECT rd.ReportID,
(SELECT STUFF((SELECT ',' + ReportColumnValue
FROM #ReportColumns WHERE ReportColumnID
IN (SELECT Value FROM F1Settings.dbo.fx_ParseToTable (ReportColumns, ','))
FOR XML PATH('')) ,1,1,'') AS COLUMNS)
FROM #ReportDefinition rd
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 27, 2015 at 12:40 pm
What I mean is that your ReportColumns column violates the first normal form that indicates:
A relation is in first normal form if the domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain. (Ref)
This is my alternative:
/* Create Report Columns table */
CREATE TABLE #ReportColumns(
[ReportColumnID] [int] IDENTITY(1,1) NOT NULL,
[ReportColumnValue] [varchar](50) NOT NULL, )
INSERT INTO #ReportColumns (ReportColumnValue)
VALUES ('Column 1'),('Column 2'),('Column 3'), ('Column 4'), ('Column 5')
/* Create Report Definition Table */
CREATE TABLE #ReportDefinition (
[ReportID] [int] NOT NULL,
[ReportColumnID] [int] NOT NULL,
)
INSERT INTO #ReportDefinition ([ReportID], [ReportColumnID])
VALUES (1,1), (1,3), (1,5), (2,1), (2,2), (2,4)
SELECT d.ReportID
,'SELECT ' + STUFF( (SELECT ', ' + QUOTENAME( c.ReportColumnValue)
FROM #ReportColumns c
JOIN #ReportDefinition id ON id.[ReportColumnID] = c.[ReportColumnID]
WHERE id.ReportID = d.ReportID
ORDER BY c.ReportColumnID
FOR XML PATH('')), 1, 2, '') + ' FROM SomeTable'
FROM #ReportDefinition d
GROUP BY d.ReportID
January 27, 2015 at 1:10 pm
Thanks for clarifying Luis, as always appreciate your insight! I'll take a look and see where I can make improvements to the process and take that back to the team (they've not even finished the low level design for this project yet) 😉
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply