March 18, 2014 at 6:07 pm
What would be the best way to get all the columns with data into one row?
Sample data:
DOB LNAME FNAME MNAME
20140109 NULL NULL NULL
NULL NULL NULL JACK
NULL NULL JOHN NULL
NULL SMITH NULL NULL
End Result:
DOB LNAME FNAME MNAME
20140109 SMITH JOHN JACK
March 18, 2014 at 7:02 pm
How do you know which values go together? Is there a primary key for the table that matches the records?
DOB LNAME FNAME MNAME
20140109 NULL NULL NULL
NULL NULL NULL JACK
NULL NULL JOHN NULL
NULL SMITH NULL NULL
End Result:
DOB LNAME FNAME MNAME
20140109 SMITH JOHN JACK
This might work...
SELECT MAX(FName) AS FName
, MAX(MName) AS MName
, MAX(LName) AS LName
, MAX(DOB) AS DOB
, RecNo
FROM
(SELECT
-- first record
'2014-01-09' AS DOB
,NULL AS LName
,NULL AS FName
,NULL AS MName
,1 As RecNo
UNION ALL
SELECT NULL, NULL, NULL,'JACK',1
UNION ALL
SELECT NULL, NULL, 'JOHN', NULL,1
UNION ALL
SELECT NULL, 'SMITH', NULL, NULL,1
-- second record
UNION ALL
SELECT '2013-03-18',NULL,NULL,NULL,2
UNION ALL
SELECT NULL,NULL,NULL,'Homie',2
UNION ALL
SELECT NULL,NULL,'Homer',NULL,2
UNION ALL
SELECT NULL,'Simpson',NULL,NULL,2
) x
GROUP BY RecNo;
This returns two records... the problem is that you need some way of tying the different records together (that's what the "RecNo" column is doing). Otherwise, not sure.
March 18, 2014 at 7:16 pm
I cant have hard coded values. I am looking to select the columns since the values will vary as different data is inserted into this table. Any other suggestions?
March 18, 2014 at 10:26 pm
The union query was just to create records I could manipulate based on the data you provided. I added the identity column because I needed a way to connect the records to "accumulate" to create a single complete record.
If you post a few dummy records with your real table structure, I can fix it so it matches your existing table structure.
Jeff Moden posted a great article on how to get the best help:
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Definitely worth reading! If you follow the instructions and post some consumable data, I'll see if I can improve my answer.
Hope this helps,
Pieter
March 18, 2014 at 11:51 pm
I prefer to write this kind of queries with this technique:
Select
(SELECT DOB FROM [Table] WHERE DOB IS NOT NULL) AS DOB,
(SELECT LNAME FROM [Table] WHERE LNAME IS NOT NULL) AS LNAME ,
(SELECT FNAME FROM [Table] WHERE FNAME IS NOT NULL) AS FNAME ,
(SELECT MNAME FROM [Table] WHERE MNAME IS NOT NULL) AS MNAME
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
March 19, 2014 at 5:02 am
Thanks guys! I will read Jeff's article and post the data structure correctly.
For the sub select solution. This may work. Is there anyway to insert the result into a temp table?
March 19, 2014 at 5:54 am
Here is the sample data. I'm only using the four columns as an example. I will be needing more once I implement. If there is a more efficient way to do this, please feel free. I am interesting in seeing. Thanks for everyone's help.
IF OBJECT_ID('TEMPDB..#SRC','U') IS NOT NULL
DROP TABLE #SRC
IF OBJECT_ID('TEMPDB..#1','U') IS NOT NULL
DROP TABLE #1
IF OBJECT_ID('TEMPDB..#2','U') IS NOT NULL
DROP TABLE #2
CREATE TABLE #SRC(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Data] [nvarchar](max) NULL,
[MsgId] [int] NULL,
[Key] [varchar](50) NULL,
[Col] [int] NULL,
[SubCol] [int] NULL )
INSERT INTO dbo.#SRC
(DATA, MSGID, , COL, SUBCOL)
SELECT 'MD', 50, 'PV1', 16, 5 UNION ALL
SELECT 'OBV', 50, 'PV1', 17, 0 UNION ALL
SELECT '728', 50, 'PV1', 18, 0 UNION ALL
SELECT 'SELF', 50, 'PV1', 19, 0 UNION ALL
SELECT 'N', 50, 'PV1', 28, 0 UNION ALL
SELECT 'A', 50, 'PV1', 40, 0 UNION ALL
SELECT '20140109', 50, 'PV1', 43, 0 UNION ALL
SELECT '000', 50, 'DG1', 0, 0 UNION ALL
SELECT 'I9', 50, 'DG1', 1, 0 UNION ALL
SELECT 'V72.85', 50, 'DG1', 2, 0 UNION ALL
SELECT 'OTH SPEC', 50, 'DG1', 3, 0 UNION ALL
SELECT 'AD', 50, 'DG1', 5, 0 UNION ALL
SELECT '1', 50, 'GT1', 0, 0 UNION ALL
SELECT 'HOT SLPHR', 50, 'GT1', 4, 2 UNION ALL
SELECT 'VA', 50, 'GT1', 4, 3 UNION ALL
SELECT '99999', 50, 'GT1', 4, 4 UNION ALL
SELECT 'SMITH', 50, 'PID', 4, 0 UNION ALL
SELECT 'JOHN', 50, 'PID', 4, 1 UNION ALL
SELECT 'JACK', 50, 'PID', 4, 2 UNION ALL
SELECT '19410605', 50, 'PID', 6, 0
/*
SELECT * FROM #SRC
*/
SELECT DATA,
MAX(CASE WHEN = 'PID' AND COL = 6 AND SUBCOL = 0 THEN DATA ELSE NULL END) [DOB],
MAX(CASE WHEN = 'PID' AND COL = 4 AND SUBCOL = 0 THEN DATA ELSE NULL END) [LNAME],
MAX(CASE WHEN = 'PID' AND COL = 4 AND SUBCOL = 1 THEN DATA ELSE NULL END) [FNAME],
MAX(CASE WHEN = 'PID' AND COL = 4 AND SUBCOL = 2 THEN DATA ELSE NULL END) [MNAME]
INTO #1
FROM #SRC
GROUP BY DATA
/*
SELECT * FROM #1
*/
SELECT DOB, LNAME, FNAME, [MNAME]
INTO #2
FROM #1
WHERE DOB IS NOT NULL
OR LNAME IS NOT NULL
OR FNAME IS NOT NULL
OR MNAME IS NOT NULL
SELECT * FROM #2
March 19, 2014 at 12:57 pm
Hi
As pietlinden asked is there a key to group the data rows. Looking at the sample data you provided, I'm guessing that it is the MSGID column. If that is the case you could do a query like the following. It's a slight variation of the first query in your sample.
SELECT MSGID,
MAX(CASE WHEN = 'PID' AND COL = 6 AND SUBCOL = 0 THEN DATA ELSE NULL END) [DOB],
MAX(CASE WHEN = 'PID' AND COL = 4 AND SUBCOL = 0 THEN DATA ELSE NULL END) [LNAME],
MAX(CASE WHEN = 'PID' AND COL = 4 AND SUBCOL = 1 THEN DATA ELSE NULL END) [FNAME],
MAX(CASE WHEN = 'PID' AND COL = 4 AND SUBCOL = 2 THEN DATA ELSE NULL END) [MNAME]
INTO #1
FROM #SRC
WHERE = 'PID' AND
COL IN (6,4) AND
SUBCOL IN (0,1,2)
GROUP BY MSGID
March 19, 2014 at 2:02 pm
masoudk1990 (3/18/2014)
I prefer to write this kind of queries with this technique:
Select
(SELECT DOB FROM [Table] WHERE DOB IS NOT NULL) AS DOB,
(SELECT LNAME FROM [Table] WHERE LNAME IS NOT NULL) AS LNAME ,
(SELECT FNAME FROM [Table] WHERE FNAME IS NOT NULL) AS FNAME ,
(SELECT MNAME FROM [Table] WHERE MNAME IS NOT NULL) AS MNAME
That does 4 scans on the table. That's terribly expensive.
--Jeff Moden
March 19, 2014 at 6:40 pm
I redesigned the structure. The PIVOT function helped me out. Thanks all for your input!
March 19, 2014 at 7:09 pm
SQLSeTTeR (3/19/2014)
I redesigned the structure. The PIVOT function helped me out. Thanks all for your input!
Just so you know, PIVOT is typically slower than CROSS TABs.
--Jeff Moden
March 19, 2014 at 7:44 pm
Never played with CROSS Tab. Do you have an example I can play around with?
I looked into books online but not sure if I'm looking at the right thing here.
A common scenario where PIVOT can be useful is when you want to generate cross-tabulation reports to summarize data. For example, suppose you want to query the PurchaseOrderHeader table in the AdventureWorks sample database to determine the number of purchase orders placed by certain employees. The following query provides this report, ordered by vendor.
Thanks for all your help and time!
March 20, 2014 at 12:09 am
Jeff Moden (3/19/2014)
masoudk1990 (3/18/2014)
I prefer to write this kind of queries with this technique:
Select
(SELECT DOB FROM [Table] WHERE DOB IS NOT NULL) AS DOB,
(SELECT LNAME FROM [Table] WHERE LNAME IS NOT NULL) AS LNAME ,
(SELECT FNAME FROM [Table] WHERE FNAME IS NOT NULL) AS FNAME ,
(SELECT MNAME FROM [Table] WHERE MNAME IS NOT NULL) AS MNAME
That does 4 scans on the table. That's terribly expensive.
You took the words right outta my mouth! :w00t:
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 20, 2014 at 12:11 am
SQLSeTTeR (3/19/2014)
Never played with CROSS Tab. Do you have an example I can play around with?I looked into books online but not sure if I'm looking at the right thing here.
http://msdn.microsoft.com/en-us/library/ms177410(SQL.100).aspx
A common scenario where PIVOT can be useful is when you want to generate cross-tabulation reports to summarize data. For example, suppose you want to query the PurchaseOrderHeader table in the AdventureWorks sample database to determine the number of purchase orders placed by certain employees. The following query provides this report, ordered by vendor.
Thanks for all your help and time!
The bible on CROSS TAB Queries (by none other than the world famous and renowned Jeff Moden no less):
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns [/url]
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs [/url]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 20, 2014 at 5:43 am
SQLSeTTeR (3/19/2014)
Never played with CROSS Tab. Do you have an example I can play around with?I looked into books online but not sure if I'm looking at the right thing here.
A common scenario where PIVOT can be useful is when you want to generate cross-tabulation reports to summarize data. For example, suppose you want to query the PurchaseOrderHeader table in the AdventureWorks sample database to determine the number of purchase orders placed by certain employees. The following query provides this report, ordered by vendor.
Thanks for all your help and time!
Dwain's post currently has a bit of a link problem so here are the links that he tried to point you to.
http://qa.sqlservercentral.com/articles/T-SQL/63681/
http://qa.sqlservercentral.com/articles/Crosstab/65048/
The first link takes you to the article that explains how both PIVOTs and CROSSTABs work, demonstates how a neat method known as "pre-aggregation" (thank you Peter Larsson) significantly improves performance, and does a performance comparison between the two methods. It also explains why I prefer CROSSTABs especially when it comes to doing slightly more complicated reporting.
The second link explains simple methods for how to make such reports using CROSSTABs dynamic in nature so that you could, for example, have a "sliding window" report based on the last 3 months that would automatically adapt based on the current data each time it is executed.
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply