Need help getting data from a Table in rows and making it into columns. 1

[DougP]

I have this sample data in two colums. the first column is the field name and the second column is the value in that field.
There will always be the same number of Field names such as SS, Fname, Lname, Addr for all of the information. in this examaple there are two records with four columns. The data is flexible so the column names will never be hardcoded using the "Create table" method, which is why the data is this way.
Can this be done in T-SQL without another table?

ColumnName Data
ID         1234
FName      Fred  
LName      Flintstone
Addr       1234 BedRock Blvd
ID         4567
FName      Barney
LName      Rubble
Addr       9938 BedRock Blvd

I want to end up with the above data in a tabular format:
SS     FName    LName      Addr
1234   Fred     Flintstone 1234 BedRock Blvd
4567   Barney   Rubble     9938 BedRock Blvd

DougP

 

[DaveInIowa]

Is there just the 2 columns or is there some means of grouping a set of data? WE can see that ID 1234 belongs to Fred but unless the order of the rows is ensured, how can SQL know it belongs to Fred and not Barney? When you perform a SELECT * on the table, does it come out in the same order as shown above?

 

[DougP]

DaveInIowa, Yes all rows will always be inserted in the same order.

DougP

 

[DaveInIowa]

But in what order will they be retrieved?

 

[gmmastros]

DougP,

I suspect that DaveInIowa is alluding to the fact that the order of data in a table is NOT guaranteed, even if it appears to be consistent. The only way to guarantee an order is if there is a value that guarantees it.

You see... the query you want probably exists, but there is absolutely no way to guarantee that is will be correct because there is no guarantee regarding the order of the rows.

By the way, what you are describing here is "almost" an EAV table. EAV is an acronym for Entity-Attribute-Value. In this case, the "Entity" part would be the unique ID. The Attribute would be FName, LName, Addr, and the Value part would be the actual value. In an EAV model, your table would look like this:

ID    ColumnName   Value
----- ------------ -----------
1234  FName        Fred
1234  LName        Flintstone
1234  Addr         1234 Bedrock Blvd
4567  FName        Barney
4567  LName        Rubble
4567  Addr         9938 Bedrock Blvd

** I always thought Fred and Barney lived next to each other, so Barney's address should really be 1236 Bedrock Blvd, Right?

If your data looked like this, then we could help you with a query that would always generate the correct results. I'm afraid to say it, but it looks like you have serious data problems on your hands.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DougP]

Ok, so If I have another table that can pass a unique number so that each "group" of records has a number as you show then how can we do it?
Yes I was in a hurry so I just keyed in that for Barney.
I have not watched that show in decades.

DougP

 

[DougP]

Or could I use a Date/Time field for an ID, so each record would the same minute, perhaps.
Then it would do double duty as a kind of date time stamp and unique ID.

DateTimeEntered  ColumnName   Value
----------------------------------
201306061220        FName        Fred
201306061220        LName        Flintstone
201306061220        Addr         1234 Bedrock Blvd
201306061221        FName        Barney
201306061221        LName        Rubble
201306061221        Addr         9938 Bedrock Blvd 


DougP

 

[gmmastros]

With the additional column, it becomes a simple matter of pivot. There are various ways to do this, but my favorite is:

Declare @Temp Table(DateTimeEntered BigInt, ColumnName VarChar(20), Value VarChar(100))
----------------------------------
Insert Into @Temp Values(201306061220, 'FName', 'Fred')
Insert Into @Temp Values(201306061220, 'LName', 'Flintstone')
Insert Into @Temp Values(201306061220, 'Addr' , '1234 Bedrock Blvd')
Insert Into @Temp Values(201306061221, 'FName', 'Barney')
Insert Into @Temp Values(201306061221, 'LName', 'Rubble')
Insert Into @Temp Values(201306061221, 'Addr' , '9938 Bedrock Blvd')

Select DateTimeEntered,
       Min(Case When ColumnName = 'FName' Then Value End) As Fname,
       Min(Case When ColumnName = 'LName' Then Value End) As Lname,
       Min(Case When ColumnName = 'Addr' Then Value End) As Addr
From   @Temp
Group By DateTimeEntered

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DougP]

But I don't know the "field" names ahead of time, I.E. SS, Fname,Lname,Addr
and there could be more or less of fields. This is completely flexible.
Min(Case When ColumnName = [highlight #FCE94F]'FName'[/highlight] Then Value End) As Fname,
And what is "Value" column?
This is a new electronic forms generation app. I am creating, to allow users to create forms with varying fields and this data is the end result.
But I want to export the results to Excel with column headings and the data underneath.
so I can't had code the field names in?
here is my new tabel per your sugesstion

UniqueID	FormName	DateEntered	ColumnName	Data
1	Main	201306060845	SS	111-99-5555
2	Main	201306060845	Fname	Doug
3	Main	201306060845	Lname	Poston
4	Main	201306060845	Addr	5633 Del Prado Dr
5	Main	201306060846	SS	837-99-3993
6	Main	201306060846	Fname	Sam
7	Main	201306060846	Lname	Smith
8	Main	201306060846	Addr	1987 South Street
9	Main	201306060847	SS	288-02-0200
10	Main	201306060847	Fname	Harvey
11	Main	201306060847	Lname	Jones
12	Main	201306060847	Addr	309 Benton Drive

DougP

 

[DaveInIowa]

You're going to need a dynamic query.

SELECT DISTINCT ColumnName FROM MyFormsTable WHERE FormName = @FormName

For each @columnName from the above query
	Build and append to your dynamic query string the [b][highlight #D3D7CF]MIN(CASE WHEN ColumnName = '@columnName' THEN Data END) As @columnName[/highlight][/b] clause as described by George above

EXEC your-dynamic-query

 

[DougP]

OK here is what I have so far, teh yellow code gives teh results
in the green code if I can figure out how to make the @ColumnName the "AS" for the @Data then I will have it?
also notice the NULLS between each set

USE SOWTimeReportingTest
Declare @FormName nvarchar(100)
set @FormName = 'Main'
Declare @ColumnName nvarchar(100)
DECLARE @DateEntered nvarchar(12)
Declare @Data nvarchar(100)
DECLARE mycursor CURSOR LOCAL FAST_FORWARD FOR 
	Select distinct [DateEntered] from  MainForm$ WHERE FormName = @FormName
	
	OPEN mycursor  
			  -- Always true  
			 WHILE 1 = 1  
			 BEGIN     
			  -- Get next record from cursor   
			FETCH NEXT FROM mycursor        
            INTO @DateEntered
            IF @@FETCH_STATUS <> 0    
				break 
			Else
				Begin
					[highlight #FCE94F]Select ColumnName,Data from MainForm$
					Where FormName= @FormName 
					And DateEntered = @DateEntered[/highlight]

                                            Select @ColumnName  = @ColumnName  + ColumnName    
					from MainForm$
					Where FormName= @FormName 
					And DateEntered = @DateEntered
					
					Select @Data = @Data  + Data   
							from MainForm$
					Where FormName= @FormName 
					And DateEntered = @DateEntered
					And ColumnName = @ColumnName
					[highlight #8AE234]Select @Data as @ColumnName[/highlight]  
				End
			end
  CLOSE mycursor 
  DEALLOCATE mycursor

give these results

ColumnName	Data
SS	111-99-5555
Fname	Doug
Lname	Poston
Addr	5633 Del Prado Dr
[highlight #D3D7CF][NULL][/highlight]
ColumnName	Data
SS	837-99-3993
Fname	Sam
Lname	Smith
Addr	1987 South Street
[highlight #D3D7CF][NULL][/highlight]
ColumnName	Data
SS	288-02-0200
Fname	Harvey
Lname	Jones
Addr	309 Benton Drive
[highlight #D3D7CF][NULL][/highlight]

DougP

 

[DaveInIowa]

Here's what I was getting at...

DECLARE @FormName VARCHAR(32) = 'Main'
DECLARE @ColumnName VARCHAR(8)
DECLARE @MinClauses VARCHAR(1024) = ''
DECLARE @Query NVARCHAR(1024)

DECLARE csr CURSOR FAST_FORWARD FOR 
SELECT DISTINCT ColumnName
  FROM MainForm$
 WHERE FormName = @FormName

OPEN csr 
FETCH NEXT FROM csr INTO @ColumnName
WHILE @@FETCH_STATUS = 0 BEGIN
	SET @MinClauses = @MinClauses + ', MIN(CASE WHEN ColumnName = ''' + @ColumnName + ''' THEN Data END) As ' + @ColumnName
	FETCH NEXT FROM csr INTO @ColumnName	
END

CLOSE csr 
DEALLOCATE csr

SET @Query = 'SELECT DateEntered ' + @MinClauses + ' FROM MainForm$ GROUP BY DateEntered'

EXEC sp_executesql @Query

 

[DougP]

SWEET !!!!! DaveInIowa have a STAR !!!!

DougP

 

[DougP]

DaveInIowa, I have one issue, sorry. If I add another form to the table(s). So I have two now PatientInfo and Students then it gets all of the DateEntered rows with empty data along with the records for the table. I tried changing the columns names to something different but that did not make a difference.
Here are the column names, I have two tables; one has the forms info. one has the data.

This is the raw data from the "main" table which has the forms and field names.

ID	FormName	FieldName	FieldType	
1	PatientInfo	SocialNum	nvarchar(100)	
2	PatientInfo	FirstName	nvarchar(100)	
3	PatientInfo	LastName	nvarchar(100)	
4	PatientInfo	Address	        nvarchar(100)	
5	PatientInfo	Address2	nvarchar(100)	
8	PatientInfo	Zip	        nvarchar(100)	
10	Students	StudentID	nvarchar(100)	
13	Students	Grade	        nvarchar(100)	
6	PatientInfo	City	        nvarchar(100)	
7	PatientInfo	State	        nvarchar(100)	
9	PatientInfo	Phone	        nvarchar(100)	
11	Students	FirstName	nvarchar(100)	
12	Students	LastName	nvarchar(100)

raw data in "Data" table contains field names and data (only shows one record from PatientInfo)

ID	FormName	ColumnName	Data	DateEntered
85	PatientInfo	FirstName	Gerry	20130607080831
86	PatientInfo	LastName	Jameson	20130607080831
87	PatientInfo	Address	290 Sunny Lane	20130607080831
88	PatientInfo	Address2	# 388	20130607080831
89	PatientInfo	Zip	        34332	20130607080831
90	PatientInfo	City	        Tampa	20130607080831
91	PatientInfo	State	        FL	20130607080831
92	PatientInfo	Phone	828-002-5445	20130607080831
95	Students	FirstName	Harvey	20130607081758
96	Students	LastName	Jones	20130607081758
93	Students	StudentID	23222	20130607081758
94	Students	Grade	        5th	20130607081758

After running your script for Students

DateEntered	FirstName	Grade	LastName	StudentID
20130607073850				
20130607080831				
20130607081758	Harvey	        5th	Jones	         23222

after running script for PatientInfo

DateEntered	Address	Address2	City	FirstName	LastName	Phone	SocialNum	State	Zip
20130607073850	5633 Del Prado Drive	Tampa	Doug	        Poston	  727-456-0020	000-00-9999	FL	33617
20130607080831	290 Sunny Lane		Tampa	Gerry	        Jameson	828-002-5445	000-00-0000	FL	34332
20130607081758				        Harvey

DougP

 

[DaveInIowa]

You just simply add a WHERE clause to your dynamic query string to select only rows having data for the form you want to process. The following should work but I'm not certain I have the number of quote characters correct.

SET @Query = 'SELECT DateEntered ' + @MinClauses + ' FROM MainForm$ WHERE FormName = ''' + @FormName + ''' GROUP BY DateEntered'

Another option would be

SET @Query = 'SELECT DateEntered ' + @MinClauses + ' FROM MainForm$ WHERE FormName = @FormName GROUP BY DateEntered'

EXEC sp_executesql @Query, N'@FormName VARCHAR(32)', @FormName = @FormName

and sp_executesql could be executed to pass in @FormName to the dynamic query as a parameter (I'm not sure I have this correct either as I'm typing this without checking in SQL Server Management Studio).

 

[DougP]

DaveInIowa, that is awesome man !!! have another star



DougP