July 31, 2009 at 11:55 pm
Hi,
I have a case where I need to show the row data as columns -
In our application, we have used defined fields - so the user can add new fields at runtime and enter data for those. Say, in the employee module, the user can add two fields Name and Age. Since fields can be added at runtime,
These user defined fields are stored in a table - TemplateFields with fields - tmpid and fieldname
The Data is stored in TemplateFieldDate with fields - tmpfieldid and data
TemplateFields contain -
tmpid | fieldid | fieldname
------------------------
1 1 Name
1 2 Age
TemplateFieldData contain-
tmpid | tmpfieldid | data
------------------------------
1 1 ABC
1 2 20
I have a requirement to show a report as
EMPID | Name | Age
--------------------------
AAA ABC 20
BBB BCD 30
I have seen PIVOT, but it is used for aggregate functions
any ideas how I can do this ?
Thanks
Suchi
August 1, 2009 at 7:01 am
Hi there,
I would use a dynamic cross-tab query for this, but I don't see where EMPID is coming from. Can you provide some sample data for this also?
Also, I don't see any foreign key in TemplateFieldData, so how are specific items in this associated with specific EMPIDs?
August 1, 2009 at 8:09 am
I agree about the dynamic cross-tab being an excellent solution to the problem. I also think this is awful database design. Sorry, but while I understand your objectives, you probably would have been better off storing all this as XML. Your implementation dooms you to writing lots of dynamic SQL and is probably going to perform poorly compared to a conventional table.
Beyond that you will quickly find that your users will define all sorts of things differently for their own purposes. One user will define "Address" and another will define "addr". One user will define HomePhone, CellPhone, BusPhone, while another will put phone1, phone2, phone3. You will never be able to tie any of that data together across users. This is the antithesis of a relational database. They might as well just be entering freehand notes in a text blob.
Usually, when someone tries this, the attribute name is included in this fashion. It still isn't good, but it is much less complicated than your setup.
declare @sample table (empid int, attribute char(10), xvalue varchar(20))
insert into @sample
select 1, 'Name', 'John' union all
select 1, 'Age', '25'union all
select 2, 'Name', 'Jane' union all
select 2, 'Age', '30'
select * from @sample
select empID
,min(case when attribute = 'Name' then xvalue else null end) as Name
,min(case when attribute = 'Age' then xvalue else null end) as Age
from @sample
group by empid
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 1, 2009 at 9:10 am
Thanks for the information on this type of database design 🙂
Here's the dynamic version:
USE tempdb
IF OBJECT_ID('#sample') IS NOT NULL
DROP TABLE #sample
CREATE TABLE #sample (empid int, attribute char(10), xvalue varchar(20))
insert into #sample
select 1, 'Name', 'John' union all
select 1, 'Age', '25' union all
select 2, 'Name', 'Jane' union all
select 2, 'Age', '30' union all
select 2, 'Attribute', 'Some value'
select * from #sample
DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT
empID'
MIN(CASE WHEN attribute = ''' + RTRIM(attribute) + ''' THEN xvalue ELSE NULL END) AS ' + RTRIM(attribute)
FROM (SELECT DISTINCT attribute FROM #sample) T
FROM #sample
GROUP BY empID'
PRINT @sql
EXEC (@SQL)
DROP TABLE #sample
April 8, 2010 at 3:27 am
Hii
In converting a Single Row to a columns:
In my case the row is consisting of gmail id's separated by commas ie aa@gmail.com,bb@gmail.com,cc@gmail.com
I wanted the SQL Query where the colums be in the form
Kindly reply ASAP
April 8, 2010 at 5:12 am
adarsh.u.tholar.b (4/8/2010)
HiiIn converting a Single Row to a columns:
In my case the row is consisting of gmail id's separated by commas ie aa@gmail.com,bb@gmail.com,cc@gmail.com
I wanted the SQL Query where the colums be in the form
Kindly reply ASAP
Here is one way of doing it.
THIS IS NOT MY CODE, I FOUND IT SOMEWHERE IN THE WEB AND I DONT REMEMBER WHO SCRIPTED IT. SO ALL CREDITS GO TO THE ORIGINAL CODER OF THIS.
IF OBJECT_ID('TEMPDB..#tmp') IS NOT NULL
DROP TABLE #tmp
CREATE TABLE #tmp (id int, string varchar(1000))
INSERT INTO #tmp (id, string)
SELECT 1, 'abcd@gmail.com, efgh@gmail.com, ijkl@gmail.com, mnop@gmail.com, qrst@gmail.com, uvwx@gmail.com, yz@gmail.com' UNION ALL
SELECT 2, 'zyxw@gmail.com, vuts@gmail.com, rqpo@gmail.com, nmlk@gmail.com, jihg@gmail.com, fedc@gmail.com, ba@gmail.com' UNION ALL
SELECT 3, 'the@gmail.com, quick@gmail.com, brown@gmail.com, fox@gmail.com, jumped@gmail.com, over@gmail.com, the@gmail.com, lazy@gmail.com, dog@gmail.com'
SELECT * FROM #TMP
;WITH test (id, lft, rght, idx)
AS
(
SELECT t.id
,LEFT(t.string, CHARINDEX(', ', t.string) - 1)
,SUBSTRING(t.string, CHARINDEX(', ', t.string) + 2, DATALENGTH(t.string))
,0
FROM #tmp t
UNION ALL
SELECT c.id
,CASE WHEN CHARINDEX(', ', c.rght) = 0 THEN c.rght ELSE LEFT(c.rght, CHARINDEX(', ', c.rght) - 1) END
,CASE WHEN CHARINDEX(', ', c.rght) > 0 THEN SUBSTRING(c.rght, CHARINDEX(', ', c.rght) + 2, DATALENGTH(c.rght))
ELSE '' END
,idx + 1
FROM test c
WHERE DATALENGTH(c.rght) > 0
)
SELECT LFT FROM TEST ORDER BY ID, IDX
IF OBJECT_ID('TEMPDB..#tmp') IS NOT NULL
DROP TABLE #tmp
April 8, 2010 at 5:19 am
And Mr.adarsh.u.tholar.b , never put your new request in form of reply to an ongoing thread, open a new thread and place forth your new request. There by many people will have a watch over it and you will 'N' number of ways to do it..
And when u are planning to open a new thread for a request, make sure you follow all the etiquettes given in the following article
DO's and DONT's while posting a request- Jeff Moden[/url]
Cheers!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply