[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Replace names of Column in group by view with a captions view

Posted on 2011-10-12
8
Medium Priority
?
219 Views
Last Modified: 2012-05-12
I have one view that looks like this

                       CUSTOM1   CUSTOM2  CUSTOM3 etc
FINANCE               210             0               4
WORKSHEET        34               3               6
etc

I have another view that has the names of these captions
CUSTOM1 - CUSTOMER
CUSTOM2 - PROJECT
CUSTOM3 - YEAR
etc

Now I want to join this Caption description view to original view so it look like this
                       CUSTOMER   PROJECT  YEAR etc
FINANCE               210             0               4
WORKSHEET        34               3               6
etc

The tables are not related so cannot do a join and the orginal table lists each of CUSTOM1 etc as Column in the table (I didn;t desing database please don't tell me about normalisation :))

Anyway have a nice neat solution I am stumped as best way to go about this?

Cheers
Damien
0
Comment
Question by:ITAS01
  • 5
  • 3
8 Comments
 
LVL 22

Expert Comment

by:Thomasian
ID: 36960178
CREATE TABLE Table1 (s varchar(100), CUSTOM1 int, CUSTOM2 int, CUSTOM3 int)

INSERT Table1
SELECT 'FINANCE',  210, 0, 4
UNION ALL SELECT 'WORKSHEET', 34, 3, 6

CREATE TABLE Table2 (col varchar(100), caption varchar(100))

INSERT Table2
SELECT 'CUSTOM1', 'CUSTOMER'
UNION ALL SELECT 'CUSTOM2', 'PROJECT'
UNION ALL SELECT 'CUSTOM3', 'YEAR'

GO

DECLARE @sql varchar(max)

SELECT @sql = ISNULL(@sql+',','SELECT ') + QUOTENAME(c.name)  + ' AS ' +  QUOTENAME(ISNULL(t2.caption,c.name))
FROM sys.tables AS t
     INNER JOIN sys.all_columns AS c ON c.object_id=t.object_id
     LEFT JOIN Table2 t2 ON t2.col=c.name
WHERE t.name='Table1'

SET @sql = @sql + ' FROM Table1'

PRINT @sql
exec (@sql)

Open in new window

0
 

Author Comment

by:ITAS01
ID: 36960243
Your example looks like it does the trick. I'll just modify for my live views and get back to you.
Thanks for prompt response

Cheers
Damien
0
 

Author Comment

by:ITAS01
ID: 36965781
Umm ok something is not right as when I substitute my view names with your table1 and table2 nothing is returned by query although no error

My Modified query
DECLARE @sql varchar(max)

SELECT @sql = ISNULL(@sql+',','SELECT ') + QUOTENAME(c.name)  + ' AS ' +  QUOTENAME(ISNULL(t2.caption,c.name))
FROM sys.tables AS t
     INNER JOIN sys.all_columns AS c ON c.object_id=t.object_id
     LEFT JOIN vwITAS_CustomCaptions t2 ON t2.PROFILE_FIELD=c.name
WHERE t.name='vwITAS_CustomUsage'

SET @sql = @sql + ' FROM vwITAS_CustomUsage'

PRINT @sql
exec (@sql)

Also my caption table columns are

PROFILE_FIELD               CAPTION
CUSTOM1                         Customer
CUSTOM2                         Project
etc 30 entries

Does it matter that I am using views as my column names in Table1 are actually aliases and dont match to Table. E.g. C1ALIAS has been given and ALIAS of CUSTOM1 in the view.

Cheers
Damien
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:ITAS01
ID: 36965787
My Table1 is
SELECT     C_ALIAS, COUNT(C1ALIAS) AS CUSTOM1, COUNT(C2ALIAS) AS CUSTOM2, COUNT(C3ALIAS) AS CUSTOM3, COUNT(C4ALIAS) AS CUSTOM4, COUNT(C5ALIAS)
                      AS CUSTOM5, COUNT(C6ALIAS) AS CUSTOM6, COUNT(C7ALIAS) AS CUSTOM7, COUNT(C8ALIAS) AS CUSTOM8, COUNT(C9ALIAS) AS CUSTOM9, COUNT(C10ALIAS)
                      AS CUSTOM10, COUNT(C11ALIAS) AS CUSTOM11, COUNT(C12ALIAS) AS CUSTOM12, COUNT(C13ALIAS) AS CUSTOM13, COUNT(C14ALIAS) AS CUSTOM14,
                      COUNT(C15ALIAS) AS CUSTOM15, COUNT(C16ALIAS) AS CUSTOM16, COUNT(C29ALIAS) AS CUSTOM29, COUNT(C31ALIAS) AS CUSTOM31, COUNT(C30ALIAS)
                      AS CUSTOM30, COUNT(CDBL1) AS CUSTOM17, COUNT(CDBL2) AS CUSTOM18, COUNT(CDBL3) AS CUSTOM19, COUNT(CDBL4) AS CUSTOM20, COUNT(CBOOL1)
                      AS CUSTOM25, COUNT(CBOOL2) AS CUSTOM26, COUNT(CBOOL3) AS CUSTOM27, COUNT(CBOOL4) AS CUSTOM28, COUNT(CDATE1) AS CUSTOM21, COUNT(CDATE2)
                      AS CUSTOM22, COUNT(CDATE3) AS CUSTOM23, COUNT(CDATE4) AS CUSTOM24
FROM         MHGROUP.DOCMASTER
GROUP BY C_ALIAS
0
 
LVL 22

Accepted Solution

by:
Thomasian earned 2000 total points
ID: 36966050
Use sys.views instead of sys.tables
CREATE TABLE Table1 (s varchar(100), CUSTOM1 int, CUSTOM2 int, CUSTOM3 int)

INSERT Table1
SELECT 'FINANCE',  210, 0, 4
UNION ALL SELECT 'WORKSHEET', 34, 3, 6

CREATE TABLE Table2 (col varchar(100), caption varchar(100))

INSERT Table2
SELECT 'CUSTOM1', 'CUSTOMER'
UNION ALL SELECT 'CUSTOM2', 'PROJECT'
UNION ALL SELECT 'CUSTOM3', 'YEAR'

GO

CREATE VIEW View1 AS
SELECT * FROM Table1 

GO

DECLARE @sql varchar(max)

SELECT @sql = ISNULL(@sql+',','SELECT ') + QUOTENAME(c.name)  + ' AS ' +  QUOTENAME(ISNULL(t2.caption,c.name))
FROM sys.views AS t
     INNER JOIN sys.all_columns AS c ON c.object_id=t.object_id
     LEFT JOIN Table2 t2 ON t2.col=c.name
WHERE t.name='View1'

SET @sql = @sql + ' FROM Table1'

PRINT @sql
exec (@sql)

Open in new window

0
 

Author Comment

by:ITAS01
ID: 36966078
Awesome that works but sorry to be a pain how do I turn this result set into a view if I copy and paste into my new view editor it complains. Sorry for newbie question.

Cheers
Damien
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 36966082
Since the column names will be dynamic, it is not possible to create a view that will meet your requirement.
0
 

Author Comment

by:ITAS01
ID: 36966087
OK no problems I have created a stored procedure instead.
Thanks for you efforts :)

Cheers
Damien
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

834 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